Power BI can connect to Microsoft Excel files in your Office 365 SharePoint sites to produce powerful reports. You can then publish those reports and create dashboards using the Power BI Service in Office 365. In this first of two posts, I’m going to cover how to create a comparative sales and expense report using a food and beverage scenario. Another highlight and focus is to cover how to rearrange Microsoft Excel table data in Power BI using a technique, unpivot, that is required to build these style of reports from the types of tables we’re often presented with when moving data from Excel to Power BI.
For reference, I’m starting with the workbook I created in my post on using INDIRECT and VLOOKUP in Excel.
Load Office 365 SharePoint Excel Data into Power BI Desktop
Personally, I don’t like the “Get Link” functionality in SharePoint now. It produces a CDN formatted URL that often does not work with other applications. So, what I do to get a link to my Excel file in SharePoint:
- Navigate to the Info pane
- Click Copy path, found towards the top
The URL will end with “?web=1”. Delete that text so you are left with a URL that ends in “.xlsx”, the file extension for Microsoft Excel workbooks. Something like;
https://mytenant.sharepoint.com/sites/projects/sales/Shared%20Documents/PowerBISalesDemo.xlsx
This is the URL you’ll specify when loading an Excel workbook from SharePoint into Power BI.
From here, launch Power BI, choose “Get data”, navigate to “Other” in the Get Data pane, and choose Web.
In the “From Web” dialog that appears, keep the “Basic” radio button selected, and specify the URL you captured above (remember, without the ?web=1). Click OK.
Next, in the Access Web Content dialog, select “Organizational account” and then click Sign in”. Provide your Office 365 credentials when appropriate.
From here, I’ll select the Chicago, Detroit, Los Angeles, and Seattle tables that were shown in the previous post linked in the intro.
Click the Edit button on the dialog to launch Power Query Editor, or if you forget and choose Load, you can click Edit Queries from the Power BI Desktop > Home ribbon.
Power Query Editor Unpivot and Data Type Change
We often find that analysis tables in Microsoft Excel have already been structured in a “pivoted” way. The descriptive data such as category and month have been broken out into columns / attributes, and the values are specified underneath. That’s just how things seem to make sense in Excel…especially when you’re working with Excel formulas to produce reports and analyze data. There’s nothing incorrect about this…it’s usually the “best” way to do things in Excel.
Unpivot Data in Power Query Editor
But, in a BI situation, data needs to be in a fact format in order to be put into a model for analysis. The attributes and their values are assigned to every row in the table. So, we need to go from this:
…to this:
To do that, we use the Unpivot Column function in Power BI. You can approach this in a couple of different ways. In this sample, we want to unpivot 11 columns (the months), and leave Category alone. So, it makes sense here to right-click the “Category” column and choose “Unpivot other columns“.
You’ll now have the table formatted as required. There are some other important cleanup steps to take, and then we’ll be ready to setup our model and produce our report.
Rename Columns and Change Data Types in Power Query Editor
Here, I’ll use a video presentation to walk through unpivot, rename columns, and changing data types.
Repeat these steps for the remaining tables and then apply. Now we’ll use the Power Query Editor to join tables together using append queries.
Power Query Editor – Add Column and Append Queries
Add Column in Power Query Editor
We want to repeat the “City” name for every record in each table respectively. As with many things in Power BI, there’s more than one way to do this, but here’s the approach I prefer:
- Open Power Query Editor
- Click the Add Column tab
- Click Custom Column
- Specify the column name and city name (as appropriate) in the Custom Column editor
Do this for every table, ensuring that the structure for every table is identical.
Append Queries in Power Query Editor
On to the final step for getting our data ready to model and derive reports.
We want all of this data in a single table so that we can produce reports across all cities. This is done with the Power Query Editor – Append Queries. Below is a demonstration of the activity as well as an example of adding the custom column described in the previous subsection:
With this done, let’s get into the modeling and reporting. However, there is quite a bit to cover, so I have put this in post part 2. Please continue to the next post.
Thanks for reading.
Categories: Microsoft Excel, Office 365 and O365, Power BI, SharePoint
Leave a Reply