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.

Click New Query –> From Other Sources –> From Facebook

Screenshot_1

Select Feed as Connection and click OK.

Screenshot_11

Right click on the sort icon in column Story, select Text Filters and click Ends With…

Screenshot_12

Type “wrote on your Timeline.” (without the quotes, but with the dot at the end), and click OK.Screenshot_13

The result of the previous step is that we get keep all the feed items with this type of stories “X and N others wrote on your Timeline.”

Now we can keep the columns story and created_time, through Home –> Choose Columns.Screenshot_14

Change the type of column created_time to Date/Time. There are several ways to do it. One of those is to right click on the header and select Change Type and then click Date/Time.Screenshot_15

Now we will extract the number from the text in column story.

Right click on the header of column story and select Split Column, then click By Delimiter..Screenshot_16

Select –Custom– in the first drop down menu of the Split Custom by Delimiter dialog, type ” and “ in the second box (without quotes), select At the left-most delimiter, and then click OK.Screenshot_17

Right click on the column story.2, select Split Column and click By Delimiter…Screenshot_19

Select Space in the first drop down menu of the Split Custom by Delimiter dialog,  select At the left-most delimiter and click OK.Screenshot_18

Change the type of column story.2.1 to Whole Number, and rename the column to Posts.Screenshot_20

We now have the extracted number from column story in the column Posts. We should make sure to increment it by one to include the person that was originally mentioned in the column story. We should also handle the edge case (very sad edge case indeed) where only one person posted on our timeline, which will result in a null value.

Click Add Custom Column in Add Column tab.

Type “Total Posts” in New column name and add the formula below in Custom column formula:

When you are done click OK.Screenshot_21

Keep the columns Total Posts and created_time. You can do it by manually deleting the other columns or by clicking Home –> Choose Columns and selecting the columns.

Change the type of Total Posts to Whole Number, and rename column created_time to Date. Screenshot_23

Now you can click Home –> Close & Load in the Query Editor, and load the data to your worksheet or Data Model and visualize the number of posts by time.

Note: If you get everyday posts on your Facebook timeline, and not only on birthdays, you can filter the data by your birthday day and month. There are several ways to keep only birthday feed items using the PivotTable, or in Power Query. Not sure how? Contact me in the comments below.

Download this workbook and find out how many Facebook friends congratulate you on your birthdays.

3 comments

  1. Jeremy BaynhamReply

    Good stuff, there is another edge case where 2 people have posted (possibly not now used, mine dated back to 2012), and they are both named in story (e.g. Fred and Sam wrote on your Timeline.) I added a calculated column instead of converting story.2.1 to Whole Number, =try Number.From([story.2.1]) otherwise 1, and typed that as Whole Number.

  2. Alex PowersReply

    Great use of Power Query will be interested to see where I end up this year versus years past.

Leave a Reply