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.
Click New Query –> From Other Sources –> From Facebook
Select Feed as Connection and click OK.
Right click on the sort icon in column Story, select Text Filters and click Ends With…
Type “wrote on your Timeline.” (without the quotes, but with the dot at the end), and click OK.
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.
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.
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..
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.
Right click on the column story.2, select Split Column and click By Delimiter…
Select Space in the first drop down menu of the Split Custom by Delimiter dialog, select At the left-most delimiter and click OK.
Change the type of column story.2.1 to Whole Number, and rename the column to Posts.
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:
= if [Posts] = null then 1 else [Posts] + 1
When you are done click OK.
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.
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.
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.
Thank you Jeremy. Good catch 🙂
Great use of Power Query will be interested to see where I end up this year versus years past.