
New Member

August 13, 2023

I have attached a workbook that has three Worksheets:
1. Original data which is stacked as well as nested.
2. My expected results after transformation
3. My attempt to clean up using power query.
Challenge: I am stuck on transforming the
1. Township (column 1 and 3, Row 9) to be a separate header with Chawama as its repeated data
2. Substation (column 19 and 21, Row 10) to be a separate header with CHW03 as its repeated data
3. Circuit Fields (Column 19 and 21, Row 10) as a separate header with A_CCT1 as its repeated data
Asking for help.


Trusted Members
Moderators
Power BI

January 31, 2022



Trusted Members
Moderators
Power BI

January 31, 2022

I noticed that you tried to construct the column headers by transposing and then merging. That could work but involves quite a few steps and I thought that if you know all the column names up front, why not create a one column table with all of them first and in the correct order. That's what you see in the table that starts in row 60. Connect to it and right-click on the column header and select 'Drill down' to create a list.
After adding the three custom columns to the main table, and moving them towards the left side, I created a list of lists (using the List.Zip function), where each list contains the old column name and the new column name.
Now, the neat trick is that you can feed this list of lists into the Table.RenameColumns function. Note that when you change some column names manually, PQ generates M-code like this:
Table.RenameColumns(#"Previous Step", {{"Column1", "aaa"}, {"Column2", "bbb"}})
The part between the double curly brackets at the end is a list of lists each containing the old and the new name. And that's exactly what we have in the zipped list I mentioned earlier. Then the code to rename all the columns in one go becomes:
Table.RenameColumns(#"Reordered Columns",OldAndNewColNames)
1 Guest(s)
