Problem with custom column/sorting

[Tigrisshark]

Hi,

Having recently moved to Office 365, I am currently jumping headlong into PowerPivots ability to create automated data-imports.

I really hope I can tap you combined ingenuity on this one:
My order figures are exported in a structure like the attached Excel-File. They come with a custom 3-row header, which I want to boil down to one row.

While searching online for a solution I came across the ingenious idea:

1. transpose the files
2. build a custom column "A&B&C"
3. removing A/B/C
4. move the custom one to the front of the table
5. transposing the data again to get a usable table

On my example this worked great and I was happy with the results. However I ran into a problem:

When creating a custom column it is always appended at the END of the table. This presents a problem because these files can differ in length. The first month may have 50 rows, the following one 75.

To sort the new header to the front of the table, I use the function

Table.ReorderColumns

These require the mention of every single column by number, eg:

{ "Header","Column1", "Column2", "Column3", ...}

But the number fluctuates- as mentioned above. So I ran into a problem on the next month: The tables are cut after the original tables numbers of columns.

Does anyone of you wise folk know a way to:

• insert the custom column directly to the front of the table, or
• sort an undefined number of columns without explicitly mentioning every single one by name/no?

I need all the information of all the headers, so simply removing them won't be a possibility.

 

[Tigrisshark]

Ok, after consulting the M-reference I found a simpler way; I'll post it here if anyone besides me runs into this problem as well:

Rather than sort, you can use the following workaround:

• Don't sort the table, but transpose it so the new header is the last row
• Use Table.reverse to flip the table. The new header is now at the top
• Use Table.RemoveLastN to delete the last 3 rows- now containing the old header
• Promote the top row as header, et voilà.

 

[r2c2]

Ok, after consulting the M-reference I found a simpler way; I'll post it here if anyone besides me runs into this problem as well:

...

Interesting find. Welcome to the world of Power Query and Power Pivot. I am sure you will find the journey quite rewarding.

There is another way to solve the problem.

1. Load your table to PQ. Make sure you delete the "Changed Type" steps if any. These are often the culprits as they hard code column names.
2. Transpose the table
3. Select first three columns, go to Transform > Merge columns. This will replace the first three columns with a merged column. You can specify a delimiter if you want.
4. Transpose again.
5. Promote first row to headers. Load data to Excel.