Transform Report to Dataset using Power Query

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


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

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *