The Data and the Problems
Let’s look at what we have been given to work with.
Structure/Issues:
- We have a list of customer’s names, article descriptions, and sales.
- Customer names are only listed for the first article description. It is assumed that all successive article descriptions belong to the noted customer until a subtotal row & new customer name is encountered.
- A total row occurs after all transactions for a single customer.
- A Grand Total row occurs at the bottom of the data.
- The sales are aggregated at the monthly level displayed as month names.
- Each month has a value for Actual along with a value for Budget.
- The headings for the scenarios “Actual” and “Budget” are stored in merged cells that traverse all displayed months. This results in a double-row header.
Output Requirements:
- Display a table with the records categorized by the scenario.
- Exclude the total/grand total rows from the final report.
- The report needs to accommodate expansion when new months are added to the data.
Maintaining the Look of the Original Data
Notice that the scenario headings “Actual” and “Budget “ are centered across the month headings.
When we bring the data into Power Query, we don’t want to disrupt this look.
Instead of merging the cells to center the scenario label, a better approach is to use the “Center Across Selection” alignment option.
This feature can be found by highlighting the desired cells and pressing CTRL-1. In the Format Cells dialog box, under the Alignment tab is a dropdown for Horizontal. It is from this dropdown that the Center Across Selection option can be found.
The advantage of this option is that the data can be stored as a proper table while still retaining the look of a custom table. (Proper tables don’t care for merged cells. They’re a bit elitist in that regard.)
Keeping Track of the Totals
Because the Grand Total row is so very far from the header rows, we will utilize a split-window view to keep track of the top and bottom of the table simultaneously.
Select View (tab) -> Window (group) -> Split.
We can scroll the top and bottom windows separately giving us a view of the extents of the data.
Prepare the Data as a Table and Send to Power Query
- Select a cell in the data, press CTRL-A to select the entire data range.
- Upgrade the table to a proper Excel Table by pressing CTRL-T and do not define headers in the table.
- Return the table to its original look by clearing the color formatting. This is done by selecting Table Design (tab) -> Table Styles (group) -> and selecting the None style in the upper-left corner of the style library.
- Remove the automatically applied generic Header Row by deselecting Table Design (tab) -> Table Style Options (group) -> Header Row.
- Rename the table “TCustomer” (Table Design (tab) -> Properties (group) -> Table Name).
- Load the data into Power Query as a Table by selecting Data (tab) -> Get & Transform Data (group) -> From Table/Range.
Transforming the Data
- Start by giving the query a better name. In the Query Settings panel (right), change the current name of “TCustomer” to “DataProper”.
- Remove the automatically applied Changed Type and Promoted Headers steps from the query.
- Fill in the missing customer names for each blank row by performing a Fill Down operation on the customer names (Column1). Select Transform (tab) -> Any Column (group) -> Fill -> Down.
The Problem with Multi-Row Headers
If our headers were confined to a single row, we could proceed to the unpivoting portion of the process.
Because we need to get the scenery names “Actual” and “Budget” associated with each month, we need to perform a bit of processing on the headers.
- Merge the customer names (Column1) and the article descriptions (Column2) using a semicolon as the delimiter into a new column named “Merged”. Select Column1 and Column2 and click Transform (tab) -> Text Column (group) -> Merge Columns. The necessity of this step will soon become obvious.
NOTE: The separator can be any character as long as it doesn’t appear in the data.
- Transpose the table by selecting Transform (tab) -> Table (group) -> Transpose.
- Perform a Fill Down operation on the scenario names (Column1).
- Promote the first row as a header row (Transform (tab) -> Table (group) -> Use First Row as Headers).
- Remove the automatically applied Changed Type
- Select the first and second columns and unpivot the other columns by clicking Transform (tab) -> Any Column (group) -> Unpivot Columns -> Unpivot Other Columns.
- Select the Attribute column and split the column by delimiter by selecting Transform (tab) -> Text Column (group) -> Split Column -> By Delimiter.
- The delimiter is a semicolon and will be split at each occurrence.
- Remove the automatically applied Changed Type.
- Rename the columns.
- Filter the Customer column to keep rows that do not end with “Total”.
- Change the data types for all columns.
- Reorder the columns as Customer – Article – Scenario – Date – Sales Value.
- Close & Load the results to an Excel Table.
Adding a New Month of Data
We need to ensure that when new months of data are added, the table updates accordingly.
If we were to insert a column between the “Actual” columns and “Budget” columns then insert values for April “Actual” and a column of April “Budget” at the end of the table, we hope that the results will update when we refresh the query.
Once the data is refreshed, we can see the April transaction in the output table.
Omitting Days with No Sales
There are rows in our table where there was no sale for an item on a given day.
We can add a step to the query that removes any rows where the sale is missing.
- Right-click the query and select Edit.
- Filter out the null values from the Sales Value
- Close & Load the results to an Excel Table.
The results are as follows:
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
