Search

DataChant

Chants of big data & bigger insights

Privacy Bug in LinkedIn API – Demonstarted with #PowerQuery #Excel and #PowerBI

Inspired by the big news that Microsoft is about to acquire LinkedIn here, and due to my past history as a privacy and security advocate in Microsoft Excel team, I thought it would be important to share with you a privacy bug in LinkedIn API and a possible violation to section 2.13 of their Privacy Policy here.

According to section 2.13 of LinkedIn privacy policy: “Companies and other entities can create pages on our Services. If you follow one of these pages, non-identifiable information about you will be provided to the page’s administrators.“. Well, today I will show you how any person and (not only administrators) can automatically extract such data, and potentially “harvest” user profiles by their engagements in any company pages.

I found this bug while I was working on this blog post. I wanted to share it with you today, after a recent response from LinkedIn, stating that the issue is an expected behavior, and not a bug.

So LinkedIn and I can agree to disagree. I still think that the issue that I will share with you in a minute is a bug, and IMHO a serious one as it allows hackers to extract data about LinkedIn users who liked or commented on ANY company’s specific update via the LinkedIn API and without the company’s permissions.

For example, here is a screenshot from an Excel workbook that takes advantage of the specific bug to extracts from LinkedIn 100 personal names,  their professional headline, and LinkedIn object ID that can lead to additional personal data such as their location.Screenshot_1.png
The hundred users above liked a specific Microsoft status update on LinkedIn (these are 100 users out of a total 2769 users who liked the specific update below). The status update below was arbitrarily selected. You can extract this information from any other company update on LinkedIn.

Screenshot_2.png

Now, as you can imagine, I don’t have any access as an administrator to Microsoft company page, so it seems “unfair” to allow me to read this data, or even worse, to allow potential abuse of such data by the company’s competitors or hackers.

You may argue, that this data should be public to anyone, as it is public to users who browse company pages in LinkedIn, but I will show you below a proof that LinkedIn intended to block this data from users who are not company page administrators.

As for company adminsitrators, it does make sense to allow them to extract such data to monitor engagement and to learn more about their audience. But right now, with the bug I will share with you, anyone can use a fake company in LinkedIn and “harvest” users by their likes and comments on companies’ updates.

Now let’s drill down to the bug itself.

Continue reading “Privacy Bug in LinkedIn API – Demonstarted with #PowerQuery #Excel and #PowerBI”

Advertisements

Analyze Facebook Reactions with Power BI

Earlier this year, Facebook introduced the new reactions Love, Haha, Wow, Sad and Angry.

Screenshot_11

In my blog post here, I shared with you the implications of the new reactions on your Excel/Power BI reports. The good news: On April this year Facebook released version 2.6 of their Graph API here, which allows you to read all the Facebook reactions from personal, page or app posts.

I have uploaded a Power BI dashboard here with an analysis of the reactions to my Facebook posts.

Screenshot_18

So here is a step-by-step tutorial for loading your profile post reactions to Power BI (You can perform the same steps with Excel using Power Query add-in in versions 2010 and 2013, or using the Get & Transform section in Data tab of Excel 2016).

Continue reading “Analyze Facebook Reactions with Power BI”

How many Facebook friends congrat me on birthdays – Find out with Excel Power Query

I know that this is not one of the most important questions to ask, but inspired by my recent birthday, I am sharing this workbook that answer the following question:

How many Facebook friends congratulate you on your birthdays? Do you increase your popularity over the years, or does age pay its toll, and your old friends feel awkward to congratulate you at this ambivalent day?

If you want to answer this question, download this workbook. Make sure you use Excel 2016, or download this Add-In if you have Excel 2010 or 2013.

Screenshot_10

To learn how to create this report, keep reading.

Continue reading “How many Facebook friends congrat me on birthdays – Find out with Excel Power Query”

Faster Matrix Mulitplication

As a followup for our recent Matrix Multiplication post here, I would like to share with you a faster query for Matrix Multiplication (Faster only relative to the previous ones I shared. Excel is probably not the best tool for big matrices, and you may still need to use other tools such as R).

I would like to thank Imke Feldmann and Bill Szysz for sharing their queries. It turns out that the use of List.Generate is quite slow in comparison to the native Power Query transformations that we will share today.

Note: If you are an advanced Power Query practitioner, you can skip to the end of this blog to see the final M expression, and download the workbook sample.

Continue reading “Faster Matrix Mulitplication”

Facebook Insights Bug

If you use Excel and Power BI Facebook connector to read page insights, you should be aware of this recent bug.

Till it gets fixed, you cannot read most of the metrics for your page using Excel and Power BI.
Thank you Tessa from Power BI team for pointing me to this bug, and to you, Borut for discovering it.

Nested Loop with List.Generate in Power Query

Recently I shared a Matrix Multiplication Power Query technique here, and explained how to use List.Generate as the core step of the multiplication.

It was fun to experiment with List.Generate and use it to implement a kind of a For-Loop. So I thought it would be cool to use List.Generate to iterate over a nested loop.

Below you can find the function that I created. You can use it to implement a kind-of-a-nested-loop in Power Query.

The arguments RowCount  and ColumnCount represent the number of iterations for the outer and inner loops, respectively. To better explain my code, you can think of it as a function that performs operations on all the cells in a table, starting with the first row, iterating over the cells of each column in the specific row, then jumping to the next row, and so forth.

fnIterateNestedLoop = (RowCount, ColumnCount, fnTransformByIndices) =>
 List.Generate(
  ()=>[i=0, j=0],
  each [i] < RowCount,
  each
   if [j] < ColumnCount - 1 then
    [i=[i], j=[j]+1]
   else
    [i=[i]+1, j=0],
  each fnTransformByIndices([i], [j])
 ),

Continue reading “Nested Loop with List.Generate in Power Query”

Power Query List.Accumulate – Unleashed

Following a reader’s request, today we will unleash the power of List.Accumulate.

The official documentation on List.Accumulate here was very confusing for me:

List.Accumulate(list as list, seed as any, accumulator as function)as any
Argument Description
list The List to check.
seed The initial value seed.
accumulator The value accumulator function.

OK. I understood the list argument quite right, but the other two arguments were post-nuclear-bomb science fiction.

So let’s try to understand the example that was used in the official page:

// This accumulates the sum of the numbers in the list provided.
List.Accumulate({1, 2, 3, 4, 5}, 0, (state, current) => state + current) equals 15

Oh, so the code above sums up all the elements in the list. That is nice. Let’s make sure the calculation was done right. 1+2+3+4+5 = 15. Yes, this is right 🙂

To prove that the code works, we can paste it to the Query Editor after creating a blank query:

Screenshot_15

But wait a minute, why should we bother using List.Accumulate when we can already sum up the numbers in a list using the function List.Sum?

Screenshot_16

So there must be something more powerful here… We will try to reveal it today and prove that List.Accumulate: More than meets the eye…

Continue reading “Power Query List.Accumulate – Unleashed”

Excel Matrix Multiplication – Replacing MMULT with Power Query

In my last Thank You post, I suggested that Matrix multiplication is not Excel’s forte. Following that post, I got a cool Power Query solution from Imke Feldmann, author of ThedBIccountant.com, that performs matrix multiplication with Power Query.

So today I decided to share with you my version of a Matrix Multiplication using Power Query. I am looking forward to hear your comments.

Before we start, if you insist on using Excel for matrix multiplication, let’s explain why you should consider the Power Query option instead of the array formula MMULT in Excel.

Continue reading “Excel Matrix Multiplication – Replacing MMULT with Power Query”

DataChant is 6 month old

Thank you for following my blog. I wanted to write something personal today to celebrate the blog’s 6 month milestone.

I started this blog 6 months ago after two great years as a senior program manager at Microsoft Excel team. With my amazing wife, who got an exciting opportunity in her pharmaceutical company’s headquarters, and four kids, we moved to Chicagoland.

As I am still waiting for my US work permit (should arrive any day), blogging here at DataChant.com was one of the things that have been keeping me sane – Especially during the last three months where the relocation-related errands subsided.

When I wasn’t blogging, or playing Fallout 4 (OMG, this game is addicting), I was advancing my knowledge in Machine Learning with a highly recommended online course from Stanford University here. Initially I considered to find some scenarios where Excel can replace Matlab, but it was too frustrating to use Excel for such scenarios (When you start using Excel to perform matrix multiplication, it’s probably the time to switch to other tools).

So 6 months have passed with 32 posts, 10,460 views and 5,365 unique visitors. I am not sure if these numbers are big for you (some of you are greater bloggers), but I am really humbled by the thought that so many people have been visiting my blog. So big thank you!

Screenshot_26

What’s coming next?

Once I get my work permit, I will start my private consulting business. I already got my first big contract, and I am excited to start it soon. I am also looking for my next dream job in Product Management, Software Engineering and Data Analytics domains at Greater Chicago area, so if you hear anything interesting, you know where to find me 🙂

See you in the next blog post, and thank you for following DataChant.com

Gil

Create a free website or blog at WordPress.com.

Up ↑