Budget: Transformations
In this section we learn how to transform our calculations into a form usable by our outputs: PivotTables and Pivot Charts. We will also use the following functions (click to learn more):
Our calculations scheduled when each budget item would be expensed, or each income item received. We need to take these results and turn them into actionable information. This means we need to summarize them and display them so users can gain insights into how to better manage their money (actions). Charts make finding problem areas easier. Pivot Charts with slicers and drill down making finding why areas are problems possible. But our calculations, as they stand at this point, are unusable by pivots. To make them useable, we must transform them into a "denormalized table".
A denormalized table combines several related (normalized) tables and joins them into one table. Below is a representation of the de-normalized table we will create with related data from different tables (tblBC, tblRC, tblBI, and Calcs) joined into one record per item.
PivotTables like denomralized tables and pivotTables' Show Details feature (drill down) works best with this type of table.
Transformation Process Overview
Our transformation process is thus:
Unpivot calculations (change crosstab structure to normal table structure)
Remove zero amounts
Combine data about each amount and date into one denormalized table.
Power Query is perfect for these tasks as long as the data is in tables. While our dynamic array looks like a table, it is not a table. Power Query also accepts named ranges. And if we make our named range look like a table, Power Query will treat it like a table. Now, with Power Query in mind, we will adjust our process like so:
Create a Named Range over Calcs that looks like a table
Load tblBC, tblRC, tblBI and Calcs into Power Query.
Complete transformation of Calcs into a proper table by
Converting Calc's first row to column headings
Unpivoting Calcs
Renaming the unpivoted columns (Date and Amount)
Removing zero amount records
Combine (join) tblBC, tblRC, tblBI and Calcs into a denormalized table.
Create Names
Before we can use Power Query, we must create a named range over our dynamic array that Power Query can interpret as a table. That name will include the array's column headings (Dates), the ID value running down the left, and the monster formula's entire SPILL range. We will start by naming the monster formula's cell and SPILL range. Afterwards we will create a name to include the monster formula's array, data headings, and item IDs. Thus, the naming tasks are:
Create the name CalcData for just the monster formula's cell and SPILL range.
Create the name Calcs to combine CalcData, IDs, and Date headers.
CalcData
This names our monster formula that we placed in Calcs!$B$6#:
Note the # sign at the end of the address. This tells Excel this is a dynamic array with SPILL range.
Calcs
This name includes the CalcData, Date headings and item IDs.
We use the OFFSET() function to return a range starting 1 row up and 1 column left of our CalcData to encompass the date headings and ID column. We then use the ROWS() function to return the number of rows in CalcData and add 1 to accommodate the Date heading and CalcData. Lastly, we use the COLUMNS() function to return the number of columns in CalcData and add 1 to accommodate the ID column and CalcData.
Load Tables to Power Query
We will need to combine our calculations with our Budget Categories (tblBC), Budget Items (tblBI), and Recurrence Codes (tblRC) so we will start Power Query with tblBC first. Navigate to worksheet Inputs, click anywhere inside tblBC, then click Excel's Data tab > From Table/Range icon.
This launches the Power Query Editor and creates a new query called tblBC.
On the left side is the Queries panel. Find tblBC.
Transform Calcs
We want to transform Calcs so click Table entry in the Content column next to Calcs.
Fix Headers
Power Query did not guess that our first row contained headers. To fix this, click Power Query's Transform tab > Use First Row as Headers icon.
Unpivot Calcs
Click the ID column to make sure it is selected (and no other columns). Then (in the Transform tab) click the dropdown arrow on the left of Unpivot Columns and select Unpivot Other Columns.
Remove Zero Amounts
This brought over all the zero amounts which is of no value. Remove then by clicking the dropdown at the right of the Attribute column and unchecking the 0 box. Click OK.
Rename Columns
Power Query applied default names to the unpivoted data. Let us change them to something meaningful. Right click Attribute and Rename it to Date. Then right click Value and Rename it to Amount.
Set Data Types
Amount
Power Query did not quite get the data types right. Let us fix that. At left of the Amount column heading is a little icon with ABC at top and 123 underneath. Click this then select Currency.
Date
The Date column must also be fixed but this will require an odd extra step to overcome a bug in Power Query (at the time of this writing) with regards to non-table data. We want this to be a Date data type but before we can make it a date, we have to make it a Date/Time data type. So to avoid an error we must click the ABC icon, select Date/Time, click the ABC icon again, and then select Date. Power Query will complain and asks if we want to replace the prior step. Click Add new step instead.
Rename Query1
I don't like the name Query1. Let us give this a more meaningful name. I like to start queries with the prefix qry so let us name this qryCalcs by typing over Query1 in the Name box in the Properties panel (on the right).
Create Denormalized Table
asdfad
Join Tables
asdf
Arrange Columns
asdf
Make Available to Excel
We are done! Click Power Query's File tab > Close & Load To...Check Only Create Connection and Add this data to the Data Model.
