Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to transform Report to Dataset using Power Query. Lets get started
Video
Sample Report
In the caption below, we have Sales Report that shows the Account Managers name, Product Name, Payment Method, Sales Amount for January to March, which is Quarter One, April to June which is quarter Two and so on to quarter four. At the end of the blog post, we want to see a dataset that contains the following columns: Quarters, Month, Account Manager, Product Name, Payment Method and Sales Amount respectively
Format Report as Excel Table & Load To Power Query Editor
To get the report into the Power Query to perform the necessary cleaning and transformation, we must importantly format the report as an Excel Table. To do that:
- Click a single cell inside the report and press CTRL + A to select the entire report
- In the Get & Transform Data group of the Data tab, click on From Table/Range
- In the Create Table dialogue box, uncheck, my table has headers
- Click OK.
The data will be formatted as an Excel Table and be loaded into the Power Query editor as we can see below
Fill The First Column using Fill Down
In the first columns, we have the name of the Account Manager such as Connor Rulibeth, Natasha Love Nicholas Dean and null which means empty cells. What we wanna do is to full down the empty cells with the Account Manager’s name from the top cells. To do that:
- Right-click on the first column
- Choose Fill and and then Down
Merge Columns
Next, we want to merge the first three columns which are Account Manager, Product Name and Payment Method. To do that:
- Select the first column, hold down the shift key and select the third column
- Right-click and select Merge Columns
In the Merge Columns dialogue box, we can choose desired separator. In this example, we going to use what is called single pipe which is |. Thereofore, select Custom from the Separator dropdown and type in the |
- Click OK.
From the screenshot below, we can see that the three columns have been merged into a single column
Transpose Report
Next, we want to use the Transpose feature to transpose the report so that we will have Quarter and Month listed as the first two columns. To do that:
- In the Table geoup of the Transform tab, click on Transpose
From the screenshot below, we can see that we now have Quarter and Month name as the first two columns
We want to fill down the first column so that we dont have to see null in the column. To do that:
- Right-click on the first column and choose Fill and then Down
Promote First Row as Headers & Unpivot Others Columns
We want to use the first row as headers. To do that:
- In the transfor tab, click on use first rows as headers
Next, we want to unpivot other columns which will translate all but the currently selected columns into attribute-value pairs
- So, we select the first two columns, right-click and choose unpivot others columns
From the screenshot below, the have the unpivot other columns attribute pairs
Split Columns
Next, we want to split the three previously merged columns based on the specified separator. To do that:
- Right-click on the merged column and select Split Columns by Delimiter
- In the Split Column by Delimiter dialogue box, all we need to do is click OK because Power Query is able to discover the previously used delimiter which is single pipe in this example.
- Click OK
From the screenshot below, we can see the splitted columns
Rename Columns & Apply Data Type
We want to rename the columns properly in this order: Quarters, Month, Account Manager, Product Name, Payment Method and Sales Amount
- To rename, double-click the header and type in the appropriate column name
- Next, in the Sale Amount column, apply Currency $ data type
Filter Account Manager that Contains Total
Based the result thus far, we can clearly see that in Product Name and Payment Method columns, we have black around the green data quality indicator. This black implies that we have empty cells within the dataset which is as a result of the Subtotal for each of the Account Manager.
To fix that easily:
- Click on the Account Manager filter button
- Uncheck anything that contains Total as seen below
- Click OK.
Problem solved and the data quqality is green all through which means there is no error nor empty cells in the dataset
Load Dataset to Excel Worksheet
The final thing we wanna do is to load the dataset back to the Excel worksheet. To do that:
- In the Home tab, click on Close & Load To…
- In the Import wizard dialogue box, Table is automatically selected. Therefore, click on OK
As we can see in the screenshot below, we have a standard dataset from the report.
See you in the next tutorial