Power BI Desktop and Facebook


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?

dashboard1

 

dashboard2

 

 

dashboard3

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

articolo1_face1

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)

articolo1_face4

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:

articolo1_face2

After a fast preview, you’ll be ready to import the data.

articolo1_face3

Power BI Desktop is really simple, you can work on three area:

 

articolo1_face7Design view. To design and create your dashboard, using the table data

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

articolo1_face8

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:

articolo1_face9

 

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:

articolo1_face10

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;””)

articolo1_face11

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.

articolo1_face12

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

articolo1_face13

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)

articolo1_face14

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)

articolo1_face15

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:

articolo1_face17

Now try with the Filled Map, inserting the place.location.country data into the Location:

articolo1_face18

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!

Comments (0)

Skip to main content