How to Unpivot Data using Excel Power Query for loading into PBCS

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.

Power Query, PBCS

 

 

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”.

Power Query, PBCS

 

 

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

Power Query, PBCS

 

 

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.

Power Query, PBCS

 

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 ggibney@gibneyconsulting.com