Search

DataChant

Chants of big data & bigger insights

Tag

Power Query

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”

Advertisements

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”

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”

Analyze Linkedin Company Page with Excel and Power BI

Your company’s page on Linkedin is a treasure trove of insightful data. In today’s post I will show you how to import this data to Excel or Power BI Desktop.

In my posts here and here, we focused on the companies-search Linkedin API call, which allowed us to extract companies by specific specialties/domains and perform analysis of industry, geolocation and correlation between different specialties.

Today we will focus on your company page on Linkedin, and learn how to extract your company’s status updates, their likes and comments, and the persons who were engaged with your page.

Continue reading “Analyze Linkedin Company Page with Excel and Power BI”

Linkedin Company Search with Power BI

Today I wanted to share with you a Power BI dashboard that allows you to search in Linkedin for companies with certain specialty.

To make it relevant for you, the dashboard shows the landscape of the companies that provide consulting services on Power BI.

Screenshot_73

If you work in such a company, make sure your company is represented in this dashboard 🙂

Continue reading “Linkedin Company Search with Power BI”

Watch our webcast at PASS Excel BI Virtual Chapter

Hi everyone,

If you missed our post here, or want to refresh your skills on Excel, Power Query and analysis of Facebook Page Insights, check out our recorded webcast at PASS Excel BI Virtual Chapter, which walk you through the creation of an Excel report that measures the engagement level of Facebook Pages.

In the demo we compare all US Election Candidates by their storytellers on Facebook.

Enjoy the movie 🙂

Blog at WordPress.com.

Up ↑