- May 9, 2018
- Posted by: Gerard
- Category: PBCS, Power Query, PowerBI
PDF Version of this blog is in the link above. Click to download.
This step by step process shows you how to unpivot data using Excel Power Query for loading into PBCS.
What is Excel Power Query?
In Excel 2010 and Excel 2013 it is an Excel Add.
In Excel 2016 it is automatically built in. It resides under the data tab under the heading “Get and Transform”.
Think of Power Query as the like the “Import Data” wizard in Excel on Steroids. It provides a data transformation toolkit built directly into Excel.
Why would you use this?
Recently a client provided us with a number of tabular reports that had historical data that they wanted to load into PBCS using the Import Data module in PBCS. In order to load the tabular reports into PBCS the data need to be unpivoted and de-aggerated.
We scratched our heads initially on how to perform this then I remember I had seen a similar type functionality in a Power Query tutorial I had taken recently.
In the example below, we will use a fictitious report but the layout is very typical of the type of report generated in the FP&A group of any large company.
Step 1: Open the report you are trying to unpivot in excel.
Step 2: Setup a named range in excel as you don’t want to lock down column headers with a table.
Select A8:H21 -> Formulas -> Defined Names -> Define Name
Enter “Report” in the Name Field -> OK
Step 3: Next load the data into Power Query.
Go to the “Data” ribbon in Excel. Go to the “Get & Transform” section and select “From Table”.
Step 4: Power Query now opens up, showing you a somewhat ugly yet informative view of the data.
Note: Every blank cell in the original data table has a null
Step 5: Select Column1 -> Transform ->Fill -> Down
Power Query’s Fill Down command fills the value above into any cell that is null, but does not overwrite any other data. The Fill Up command works the same way but fills up instead of down.
The account classification is now looking better.
Step 6: Now to Transpose the data.
Go to Transform -> Transpose
We have flipped the rows to the columns and the columns to the rows. In the PBCS Smartview world we just did Pivot.
Step 7: Select Column1 -> Transform -> Fill -> Down
Step 8: Select Column1-> hold down Ctrl -> select Column2 -> Transform -> Merge Columns
Set a Custom delimiter of a | (pipe) character -> OK
Step 9: Now you can flip the data back and promote the new headers
- Go to Transform -> Transpose
- Go to Transform -> Use First Row as Headers
- Right-click the “|” column -> Rename -> Class
- Right-click the “|_1” column -> Rename -> Account
Step 10: Filter the Account Column to remove the null values
Step 11: Select the Class and Account columns.
Right-click the selected headers -> Unpivot Other Columns
Now you have an unpivoted list
Step 12: Finally, you need to split the Attribute Column back into its respective pieces and clean up the column headers
Right-click Attribute column -> Split Column -> By Delimiter -> Custom -> | -> OK
Right-click the Attribute.1 column -> Rename -> Month
Right-click the Attribute.2 column -> Rename -> Measure
Select the Class, Account, Month and Measure columns -> right-click -> Change Type -> Text
Right-click the Value column -> Change Type -> Decimal Number
Right-click the Value Column -> Rename -> Amount
Go to Home -> Close & Load to load the data into excel.
The query is now finished, unpivoted and ready to load to PBCS. In fact this process will work for unpivoting data for Essbase, Hyperion Planning and HFM.
By the way this process can be setup as repeatable and fully automated.
If you have any questions please feel free to contact me at firstname.lastname@example.org