Search

DataChant

Chants of big data & bigger insights

Tag

Excel

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”

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”

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

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”

Analyze Facebook Insights with Excel

Update 6/6/2016: Due to a Facebook bug here, it is not possible to read private Insights from your page, luckily the public metrics are still available. So this tutorial is still relevant.

Finally!!! I have waited 9 months for this feature to work in Excel (here is the proof). If you have the latest Excel on Office 365 version 16.0.6568.2036, or the latest Power Query Add-In on earlier version of Excel, you can now create amazing Facebook Insights reports and dashboards on Excel.

Recently I have shown you here and here how to create such reports using Power BI Desktop. There are also some Facebook API bugs here and here that are not fixed yet by Facebook, but after waiting 9 months, I will not let minor obstacles to stop me.

In today’s tutorial, I will show you the basic steps to create your first Facebook Insights report. We will assume that you have Facebook permissions to read the Page Insights, but this blog is also relevant for (partially) analyzing pages you don’t own.

Before we begin, here is a short teaser and a proof that this tutorial can help you. The following screenshot shows the number of shares for the official Microsoft Excel facebook page by top countries and date. As both of us don’t own this page, but can retrieve such data for analysis, must be a strong enough incentive to continue reading, right?

Screenshot_36

Continue reading “Analyze Facebook Insights with Excel”

How the new Facebook like buttons: love, haha, wow, sad and angry affect your Power Query reports

Few days ago Facebook introduced 5 new response buttons to enrich our engagement options. Clicking the “like” button is no longer the only option on Facebook, your fans can now respond with “love”, “haha”, “wow”, “sad” and “angry” reactions to your company’s posts.

If you analyze your brand on Facebook, you are probably wondering now how to measure the new reactions. Wouldn’t it be awesome if we could add the different reactions to our reports, and compare positive / negative  reactions?

For those of you who analyze fan engagement using Facebook Graph API, and use Excel, Power Query or Power BI to import the number of likes from your page posts, please continue reading to understand how you are affected. Because you are (to give you a hint – here we could use a sad or angry Facebook button to reflect the current situation).

Continue reading “How the new Facebook like buttons: love, haha, wow, sad and angry affect your Power Query reports”

Blog at WordPress.com.

Up ↑