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.
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.

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.

Analyze Facebook Reactions with Power BI

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

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.

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).

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.

To learn how to create this report, keep reading.

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.

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])
 ),

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:

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?

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…

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.