Power BI – Sales and Expense Reports – Prepare Excel Tables from Office 365 SharePoint

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

ExcelCopyPath

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.

GetWebData

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.

AccessWebSignIn

From here, I’ll select the Chicago, Detroit, Los Angeles, and Seattle tables that were shown in the previous post linked in the intro.

PowerBISelectTAble

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:

UnpivotedData

…to this:

PivotedData

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“.

UnpivotColumns

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

PowerQueryCustomColumn.png

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 EditorAppend 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.

 

Advertisements


Categories: Microsoft Excel, Office 365 and O365, Power BI, SharePoint

Tags: , , , ,

1 reply

Trackbacks

  1. Power BI – Sales and Expense Report – Negative Numbers with DAX CONTAINSSTRING – Westmorr Consulting

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: