Hello my fello Datanuts,
I am looking for some guidance on the best way to transpose a wide excel dataset into a long table in powerquery.
Background
I have inherited a profitability report that is fed from a spreadsheet populated by the most obscure and complex SQL setup I have ever seen (and I am not a SQL expert (yet!)). I know these things are always best done at the source, I am just unable to decode the horrible mess that was created before time began 😖
The Challenge
The first 4 columns of the data are company name a a few ID columns, which are then followed by blocks of columns (6 Cols) which hold the data for a particular month identified by a Billing Period ID, and each subsequent billing period has the same set of columns. And then at the end of the wide table I have a few other date fields that I need to keep.
The Data
Download Excel File
My Dirty Fix
I did manage to get a dirty solution in place but it did not feel right, elegant or even dynamic enough given that there will be a number of excel files to merge into a single report.
- Create an Index Column (Company ID)
- Duplicate query and remove all billing period blocks (This became my new base table)
- Duplicate orignal table and remove all other columns except COMPANY ID and 1st Billing Period Block of Columns
- Repeat for all Billing Periods
- Append all billing periods to base table
While this may work in a most simplistic way, i am sure that there is a better way to achieve this, and I am hoping you folks can offer some guidance. Especially given that I would ideally like this to work for multiple excel files.
Thanks in advance.
David
