In today’s post, I’ll share with you few cool tips and tricks that helped me in the creation of the Force Graph below. If you are a DataChant subscriber, you will get the “source code” emailed to you.
The report connects to Facebook using the Graph API. It starts with three “root” Facebook Pages: Power BI, Tableau and Qlik, and collects general information such as page picture, and number of fans. Then it finds which Facebook pages are liked by the “root” pages, collects information on these pages, and extracts all the pages that they like.
The result is a graph that starts with three root nodes (Power BI, Tableau & Qlik), and traverses through their 1st level pages till it reaches the 2nd level ones.
The report itself is relatively advanced, and it was a significant effort to create it, so I am not sure I will write a full step-by-step tutorial (There are so many other things to share with you, and the time is a bit challenging).
Before we continue, take the time and subscribe to DataChant for an exclusive access the actual Power BI Template!
So I can impress my boss with the visuals, but what else?
The starting point of this report was meant to be eye catching. But let’s enter the cockpit and start the flight through this galaxy of Facebook Pages. Hopefully we will find some meat on the bone.
The core table that is used in this report has the following columns: Source, Target & Parent (the “root” page). You will find the terms “source”, “target” and “parent” in many visual titles. The column source contains all the Facebook pages that were found to like other Facebook pages. The column target contains the pages that were liked by the pages in source. And parent will always contain one of the three “root” pages.
Check out the upper left slicer (This is the Chiclet Slicer custom visual). You can click on any of the three “root” pages: Power BI, Qlik & Tableau (or on any combination of the three by pressing the CTRL key). Click on Power BI to filter the Force Graph and reveal the tree-shaped graph whose root is Power BI, and his leafs are its 1st and 2nd distance pages.
You can see that the second slicer was also filtered. It now shows the pages that are liked by Power BI (the central node is called “microsoftbi”, this is the Facebook object name of Power BI page).
Note that the second slicer shows only pages that like other pages. So for instance, Microsoft Excel page is not shown in the slicer, because it has no liked pages.
Using the CTRL key you can select multiple sources in the second slicer. For example, clicking Power BI and Office will show these pages with all their targets.
You can clear the Chiclet slicer’s selections by clicking the little eraser icon at the upper right corner of the slicer.
Let’s clear the 2nd slicer and select all the “root” pages. Select 1 in the distance slicer and see how the Force Graph reveals 3 disconnected trees with all the pages that the 3 BI vendors like. It is clear that none of the vendors like each other 🙂
You can scroll down the second slicer and select any of the source pages. For example, let click on Nasdaq and see how many pages they like.
The table at the bottom shows you how many fans the selected pages owns. You can even click the little “link” icon to go directly to the Facebook page.
Finally, there is a dedicated page that let you navigate from the “root” pages to the sources and their targets (ordered by the number of fans). I think you will find this page much more useful than the Force Graph.
Tips & Tricks to connect to Facebook
Let’s start with the most important piece in this report – Its Facebook API calls. I didn’t use the Facebook connector as it usually fails to satisfy my peculiar requirements. Instead, I used the Web connector, and obtained the API token (highlighted in the screenshot below) from the Facebook Graph API Explorer.
The secret recipe!!!
It is amazing that a single API call is all it takes to load so much data for today’s report. Here is the Facebook URL that you will need (I broke it into multiple lines to make it better readable):
https://graph.facebook.com/v2.7/microsoftbi?
fields=name,picture,username,fan_count,about,likes.limit(50).order(ranked)
{
name,picture,username,fan_count,about,likes.limit(100).order(ranked)
{
name,picture,username,fan_count,about
}
}
& access_token=[YOUR ACCESS TOKEN]
You start the journey from ANY other Facebook Pages
You can wrap the the Facebook URL call above in a Query Function that will accept the Facebook Page object name (e.g. microsoftbi for Power BI page), and will use Web.Contents to extract the data.
(page) =>
let
url =
"https://graph.facebook.com/v2.7/" & page &
"?fields=name,picture,username,fan_count,about,likes.limit(50).order(ranked){name,picture,username,fan_count,about,likes.limit(100).order(ranked){name,picture,username,fan_count,about}}&" &
"access_token=" & ACCESS_TOKEN,
Source = Table.FromRecords({Json.Document(Web.Contents(url))})
in
Source
Let’s name the function above as FnGetPageInfo.
You start the journey from ANY other Facebook Pages
You can create the following query that will call the function above on the the Facebook pages that are included in the Source list.
let
Source = {"microsoftbi", "tableau", "qlik"},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "page"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each FnGetPageInfo([page])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"name", "picture", "username", "fan_count", "about", "likes", "id"}, {"name", "picture", "username", "fan_count", "about", "likes", "id"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"page", "parent"}}),
#"Expanded picture" = Table.ExpandRecordColumn(#"Renamed Columns1", "picture", {"data"}, {"data"}),
#"Expanded data" = Table.ExpandRecordColumn(#"Expanded picture", "data", {"url"}, {"url"}),
#"Added Custom1" = Table.AddColumn(#"Expanded data", "distance", each 1)
in
#"Added Custom1"
If you like Force Graphs, and you are curious to find out how the report will look on your favorite companies or brands, please change the object names in bold to your preferable pages.
Source = {"microsoftbi", "tableau", "qlik"},
A comment for DataChant subscribers: To quickly change the “root” pages in the Power BI Desktop Template, open the Advanced Editor of query Base1. You will find the queries under the groups Functions and Base:
Feeding the Monster
If you try the create the report by yourself, you can use the Query Editor to transform the query above into a series of queries which will eventually append all the source, target and parent pages into a single table. This part is where I preferred to skip, as it takes lots of transformation steps, and you can subscribe to DataChant and find all the relevant queries in the template that I shared.
Eventually you will need to create a table with the source/target pairs, as shown in the table below. I also added the parent column to allow me to slice and dice the Force Graph from a Chiclet Slicer by selecting specific the root pages.
Now you can add a new Force Graph visual (Download it from here), and drag and drop source to Source and target to Target as shown in this screenshot (You can also use parent as Link Type, and then customize the visual to color the links by their types, but this feature is not mature enough and lacks the ability to customize the colors).
Finally, if you prefer the exploration experience, you can change the visual to a Network Navigator. Download it here. BY default the Network Navigator doesn’t show the labels, but you can switch the Labels on, as highlighted in this screenshot:
Hope you find this report useful. One thing is for sure. You can use it as a screensaver or as a way to impress your boss with your Power BI magical skills.
Enjoy the flight through the Page Affinity galaxy.
