Tutorial: Facebook analytics using Power BI Desktop
In this tutorial you learn how to import data from Facebook and use it in Power BI Desktop. You'll connect and import data from the Power BI Facebook page, apply transformations to the imported data, and use the data in report visualizations.
Connect to a Facebook page
This tutorial uses data from the Microsoft Power BI Facebook page (https://www.facebook.com/microsoftbi). You don't need any special credentials to connect and import data from this page except for a personal Facebook account.
Open Power BI Desktop and select Get data in the Getting Started dialog, or in the Home ribbon tab, select Get Data and then select More....
In the Get Data dialog, select Facebook from the Online Services group, and then select Connect.
A dialog appears to alert you to the risks of using a third-party service.
Select Continue. The Facebook dialog box appears.
Type or paste the page name microsoftbi into the Username text box, select Posts from the Connection dropdown, and then select OK.
When prompted for credentials, sign in to your Facebook account, and allow Power BI access through your account.
After connecting to the Power BI Facebook page, you see a preview of the page's Posts data.
Shape and transform the imported data
You want to see and show which posts have the most comments over time, but you notice in the Posts data preview that the created_time data is hard to read and understand, and there is no comments data at all. You need to perform some shaping and cleansing of the data to pull the most out of it. You can use the Power BI Desktop Power Query Editor to edit the data, before or after importing it into Power BI Desktop.
Split the date/time column
First, separate the date and time values in the created_time column to be more readable.
In the Facebook data preview, select Edit.
The Power BI Desktop Power Query Editor opens in a new window and displays the data preview from the Power BI Facebook page.
Select the created_time column. Note that it is currently a Text data type, denoted by an ABC icon in the column header. Right-click the header and select Split Column > By Delimiter in the dropdown, or select Split Column > By Delimiter under Transform in the Home tab of the ribbon.
In the Split column by delimiter dialog, select Custom from the dropdown, enter T (the character that starts the time part of the created_time values) in the input field, and select OK.
The column splits into two columns that contain the strings before and after the T delimiter and are named created_time.1 and created_time.2, respectively. Note that Power BI has automatically detected and changed the data types to Date for the first column and Time for the second column, and formatted the date and time values to be more readable.
Rename the columns by double-clicking each column header, or selecting each column and then selecting Rename in the Any Column Group of the Transform tab in the ribbon, and typing new column headers created_date and created_time, respectively.
Expand the nested column
Now that the date and time data are the way you want them, you'll expose comments data by expanding a nested column.
Select the object_link column and then select the icon to open the Expand/Aggregate dialog. Select connections, and then select OK.
The column heading changes to object_link.connections.
Again select the icon at the top of the object_link.connections column, select comments, and then select OK. The column heading changes to object_link.connections.comments.
Select the icon at the top of the object_link.connections.comments column, and this time select Aggregate instead of Expand in the dialog box. Select # Count of id, and then select OK.
The column now displays the number of comments for each message.
Rename the Count of object_link.connections.comments.id column to Number of comments.
Select the down arrow next to the Number of comments header and select Sort Descending to see the Posts sorted from most to fewest comments.
Review query steps
As you shaped and transformed data in the Power Query Editor, each step was recorded in the Applied Steps area of the Query Settings pane at the right side of the Power Query Editor window. You can step back through the Applied Steps to see exactly what changes you made, and edit, delete, or rearrange them if necessary (although this can be risky, because changing preceding steps can break later steps).
After applying the data transformations so far, your Applied Steps should look like the following:
Underlying the Applied Steps are formulas written in the Power Query Language, also known as the M language. To see and edit the formulas, select Advanced Editor in the Query group of the Home tab of the ribbon.
Import the transformed data
When you are satisfied with the data, select Close & Apply > Close & Apply in the Home tab of the ribbon to import it into Power BI Desktop.
A dialog displays the progress of loading the data into the Power BI Desktop data model.
Once the data is loaded, it appears in the Report view as a new Query in the Fields list.
Use the data in report visualizations
Now that you have imported data from the Facebook page, you can quickly and easily gain insights about your data by using visualizations. Creating a visualization is easy; just select a field or drag it from the Fields list onto the report canvas.
Create a bar chart
In Power BI Desktop Report view, select message from the field list, or drag it onto the canvas. A table showing all post messages appears on the canvas.
With that table selected, also select Number of comments from the Fields list, or drag it into the table.
Select the Stacked bar chart icon in the Visualizations pane. The table changes to a bar chart showing the number of comments per post.
Select the ellipsis (...) in the upper right of the visualization, and then select Sort by Number of comments to sort the table by descending number of comments.
Note that the most comments were associated with Blank messages (these posts may have been stories, links, videos, or other non-text content). To filter out the Blank row, select message (all) under Filters at the bottom of the Visualizations pane, select Select All, and then select Blank to deselect it. The Filters entry changes to message is not (Blank), and the Blank row disappears from the chart visualization.
Format the chart
The visualization is getting more interesting, but you can't see much of the post text in the chart. To show more of the post text:
Using the handles on the chart visualization, resize the chart to be as large as possible.
With the chart selected, select the Format icon (paint roller) in the Visualizations pane.
Select the down arrow next to Y-axis, and drag the slider next to Maximum size all the way to the right (50%).
Also reduce the Text size to 10 to fit more text.
The chart now shows more of the post content.
The X axis (number of comments) of the chart does not show exact values, and looks lost at the bottom of the chart. You decide to use data labels instead.
Select the Format icon, and then select the slider next to X-axis to turn it Off.
Select the slider next to Data labels to turn them On. Now the chart shows the exact number of comments for each post.
Edit the data type
That's better, but the data labels all have a .0 decimal place, which is distracting and misleading, since Number of posts must be a whole number. You need to change the data type of the Number of posts column to Whole Number.
To edit the data type, right-click Query1 in the Fields list, or hover over it and select the More options ellipsis (...), and then select Edit Query. You can also select Edit Queries from the External data area of the Home tab in the ribbon, and then select Edit Queries from the dropdown. The Power BI Desktop Power Query Editor opens in a separate window.
In the Power Query Editor, select the Number of comments column, and change the data type to Whole Number by either:
- Selecting the 1.2 icon next to the Number of comments column header, and selecting Whole number from the dropdown, or
- Right-clicking the column header and selecting Change type > Whole Number, or
- Selecting Data type: Decimal Number in the Transform group of the Home tab, or the Any Column group of the Transform tab, and selecting Whole Number.
The icon in the column header changes to 123, denoting a Whole Number data type.
Select Close & Apply, or just Apply to apply the changes while keeping the Power Query Editor window open. Once the changes load, the data labels on the chart become whole numbers.
Create a date slicer
You want to visualize the number of comments on posts over time. You can create a slicer visualization to filter the chart data to different time frames.
Click a blank area of the canvas, and then select the Slicer icon in the Visualizations pane. A blank slicer visualization appears.
Select the created_date field from the Fields list, or drag it into the new slicer. The slicer changes to a date range slider, based on the field's Date data type.
Move the slider handles to select different date ranges, and note how the chart data filters accordingly. You can also select the date fields in the slicer and type in specific dates, or choose them from a calendar popup.
Format the visualizations
You decide to give the chart a more descriptive and attractive title.
- With the chart selected, select the Format icon, and select the dropdown arrow to expand Title.
- Change the Title text to Comments per post.
- Select the dropdown arrow next to Font color, and select a green color to match the green bars of the visualization.
- Increase the Text size to 10, and change the Font family to Segoe (Bold).
Experiment with other formatting options and settings to change the appearance of your visualizations.
Create more visualizations
As you can see, it's easy to customize visualizations in your report to present the data in ways that you want. For example, try using the imported Facebook data to create this line chart showing number of comments over time.
Power BI Desktop provides a seamless end-to-end experience, from getting data from a wide range of data sources and shaping it to meet your analysis needs, to visualizing this data in rich and interactive ways. When your report is ready, you can upload it to Power BI service and create dashboards based on it, which you can share with other Power BI users.