With this post I'll show you how the integration between Power BI Desktop and Facebook. The original aim was to write on how to integrate Power BI with something more enterprise like System Center Configuration Manager, Service Manager or CRM, but probably not many would have been able to test the effectiveness of Power BI... So let's start with something more social and cool and then we'll concentrate on the business side of the Power BI.
What is Power BI? Power BI Desktop puts visual analytics at your fingertips with intuitive report authoring. Drag-and-drop to place content exactly where you want it on the flexible and fluid canvas. Quickly discover patterns as you explore a single unified view of linked, interactive visualizations.
What you'll be able to do after reading the article?
Let's start 🙂
Download Power BI Desktop for free and install it
Once installed, you need to get connected with your data. Power BI permits you to get data from almost every source: SQL Databases, Analysis cubes, Text Files, Websites, JSON, webservices and so on... In this example we're going to connect with the Facebook public API, available by default on PowerBI. Click on Get Data and choose Facebook
Sign in with your data in order to connect to Facebook, and authorize Power BI to read your data (without authorization you cannot export data from Facebook)
Now you have to choose what to retrieve from the Facebook API; Imagine Facebook as a database. You can access to all the tables related to you, these tables are organized by posts, comments, likes, books, places, friends etc... Of course every table is related to another one, for example the table comments is related to the table friends that is related to the table likes. At this point you have to choose the table from which you want to take data. In an advanced scenario, you can choose one or more tables and relate it automatically or manually, but that's another story. I'll choose Feed as this contains everything about my Facebook profile:
After a fast preview, you'll be ready to import the data.
Power BI Desktop is really simple, you can work on three area:
Table view. To analyze and modify your imported data. Once you import the data, rename the query name with a more confident name (i.e: Feed, instead of Query1)
Relationship View. To create relationships between tables. (this is useless now, as we've only 1 table and nothing to be related)
Now I've imported data, I can start to design my dashboard. The first example will be related to data available by default after the import. This graph would be about which types of post I use to make on facebook (i.e.: picture upload, link, posts, comments..). Go on the design area and choose the Funnel graph
Just drag and drop the status_type on the Group and the Id on the Values and add a simple filter to remove blank actions:
Second example will be about data we have, but not in the right format: I want to show how many posts I've done per year, but the only useful column contains dates in this format:
So I've to modify tha dates, before to crate the graph. In order to do this, I've to create a new Column Click on the Table name (Query1 by default, but I renamed in Feed) and choose add Column. Now remove everything from the input bar and use a DAX expression (you can find more information about DAX here) to create new column with data taken from the created_time column): Year = REPLACE(Feed[created_time];5;20;"")
Now go back to the design area, choose a bar graph and put Ids on value and the new column Year on Axis, to have the post's number per year.
The last example will be about data we have as relationship and we need to extend in order to create graphs. I want to create a graph showing from which location I use to post. After a quick look at my table, I've data about posts, but nothing about locations. This is not completely true: clicking on Edit Queries, in the table area, I'll be able to go through relationships Facebook's Api offering, to extend my original table. For example I can ask for more data about my likers, about what people uses to share with me or more about places!
Click on the Feed query, then on Edit Queries and search for the place column
Click on the extend icon just beside the column name and choose the new column you want to show (this will show the custom place name, i.e.: my house! or AC\DC concert)
After clicking ok, click on the place.location extend icon, and choose the new column related to the place location (this will unblock the GPS coordinates, country name and more precise data)
Finally, click on Close & Apply and wait for Power Bi to retrieve all the data from the Facebook api.
Before to create the graph, I want to be sure that Power BI will tag my data correctly using the right categorization. Click on the place.location.country column go on the Modeling menu and on Data Category, choose Country/Region. Do this for other data needs to be categorized (Longitude, Latitude or real numbers)
Well now it's time to go on the design area: choose the Map and put place.location.city and id in Location and Values:
Now try with the Filled Map, inserting the place.location.country data into the Location:
Feel free to download my PowerBI_Facebook dashboard from here, connect your account and enjoy Power BI Desktop. On next article we'll create a dashboard using the Cube data from the System Center world.
PS: in order to change the Facebook Account Credential provided in my PowerBI_Facebook, you have to put your data in File -> Options and Settings -> Data source settings
If you have suggestions write me on comments!