Search

DataChant

Chants of big data & bigger insights

Tag

Power BI

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

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”

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”

Presidential Word Cloud in Power BI – Part 2

As promised, today we will continue working on our Presidential Word Cloud that we have started here, and will focus on two important and useful techniques which are not only relevant for Facebook analysis in Power BI:

  1. We will learn how to extract words from textual columns without splitting the column to many columns.
  2. By splitting messages to single words, our Word Cloud visual in Power BI will work better, we will be able to resize it, and drill down from a single word to its original message.

Oh, and the relation to Ghostbusters will be revealed at the end.

To recap our current challenge – In our last tutorial we have created a Word Cloud which is not resizable.

Screenshot_1

To fix this limitation, we will need to create a new column in our query for the individual words, and feed this column to the Word Cloud visual.

Let’s start.

Continue reading “Presidential Word Cloud in Power BI – Part 2”

Presidential Word Cloud in Power BI

One of the visualizations that I really love is tag cloud, or Word Cloud as it is called in Power BI. It usually grabs the viewer’s attention and provokes emotional reactions that can help you to touch your audience. I have used it fervently in key presentations in the past (Here is a great website to create tagclouds for ad hoc presentations), and was eager to find a good excuse to use it, when Power BI announced the Word Cloud custom visual.

So today we will build a Word Cloud 🙂

To make it interesting, I thought it would be cool to continue our Facebook series and build a Power BI report with a Word Count of the Facebook posts of all the US Election Candidates. The tutorial will have two parts. Today we will focus on the first one that builds a quick and dirty word cloud report. To see what you can get after we go through the second part go here and enjoy the finished dashboard on Power BI.

In this tutorial you will learn how to extract your posts from your brand’s Facebook Page and load the words into Power BI (Or Excel, if you are familiar with the Power Query / Get & Transform user interfaces). You will also learn some cool tricks that will help you to extract multiple offsets from Facebook Graph API, and to load multiple pages (using the query function technique).

Continue reading “Presidential Word Cloud in Power BI”

Blog at WordPress.com.

Up ↑