This is part 2 of the Getting Started with Power BI series.
Power BI is a powerful reporting and dashboarding tool in which organizations of all sizes can leverage to tell their data story. This series walks through the creation of building out a social media dashboard. Here we look at how to pull and manipulate the data from Power BI.
Pulling in Data
Getting data is very easy and you have several options. Once you have Power BI Desktop open, just click on the “Get Data” icon in the ribbon.
Note: Click on the icon to get the full list, the text drop down will get you the most commonly used.
For this tutorial, I will be selecting “Online Services” and pulling in my Facebook data. I assume that most people have a Facebook account or company website that they could use to follow along with.
Once you selected Facebook, and clicked on “Connect”, and continue on through the 3rd party warning, you will end up at:
Here you can just leave it “Me”, and it will bring in your data or enter a company’s website username. For this tutorial, I will be selecting “Feed” for the connection.
Manipulating Data
Once the data is pulled, select the “Edit” button.
This will open the query editor. The first thing I will change is the default name of the query, so that if I decide to bring in other connections or data sources I will know what data I’m working with. To do this, on the right hand side you will see the Query Settings. Here, I have changed the name from “Query1” to “FeedQuery”.
Changing data types
When data is imported, you can see the data types that are assigned to the column and if you need to, correct any data types that might not be correctly associated. So, in this case, when looking at the “created_time” and “updated_time” columns you will notice the icon of the column is [ABC] and that in the Ribbon section the Data Type states that it is text.
I’ve changed these two columns from Text to Date/Time, just by clicking on the Data Type: Text drop down arrow in the Ribbon and selecting Date/Time for each of these columns.
What you will now notice in the “Query Settings” is what changes have been applied to this query. So now every time we refresh the data, the query will also apply the following steps to the data as well.
Adding Columns
Now adding a column at this level would use Power Query logic https://msdn.microsoft.com/en-us/library/mt211003.aspx . In most cases, I would have used DAX logic to add simple manipulations and logic when adding a column, but in this situation the likes and comments columns are tables. I think it would be interesting to get a simple count on these.
First, go to the “Add Column” tab and select “Add Custom Column” button.
This will bring up the option to enter the name of the new column and the Power Query language. You will also have the list of available columns that you can use, provided in the dropdown section.
For the first new custom column, I will be calling it “LikesCnt”. For the formula, if you want to cut and paste the code: if [likes] = null then 0 else Table.RowCount([likes])
What I’m doing here is first checking to see if the [likes] field/table for that row is null, if it is then I am setting a default value of 0 (zero). In the cases it is not null, then I’m counting how many rows that inner table has.
Now again, a new column called “CommentCnt” for the comments: if [comments] = null then 0 else Table.RowCount([comments])
Let’s save for now – go to the “Home” tab and click on the “Close & Apply” button and give it a minute to refresh the data. The next article in this series we’ll start adding visuals.
The next article in this series we’ll start adding visuals. Read Part 3.
