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.
See if you can follow the steps in the queries that I added to your file. You may want to do some clean-up (removing some characters, setting data types etc.) but the core solution is there.
Come back here if you get stuck.
Yes indeed I have the core solution. Thank you very much. I have tried to follow but am stuck on the what is happening with the List. It is a black box for now. Please open it up for me.rn rn
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)
