I didn’t realize reordering columns in Power Query could be so complicated. Thanks to Gil Raviv for his solution.

I searched for a solution online. I found a reference to Gil Raviv’s solution from his book ‘Collect, Combine, and Transform Data Using Power Query in Excel and Power BI’. I already had the book but hadn’t read that part!

Files: Excel & PBIX

Gil’s solution applied to my case:

Requirement

I’ve simplified my real life case to focus on the column reordering issue:

Add an Index column that must always be the left most column in the table. Column headers may be renamed and/or increase in number on refresh.

Source Data Layout

Source data is an Excel table that looks like this:

…but column headers could change (spelling and number) and look like this:

Required Output

Data model table output has Index column on left followed by columns:

I’ll ignore obvious DAX challenges with constantly changing columns.

Solution

Quick Solution Breaks

I added an index, moved it to column position 1, sent it to data model as a table.

let
    Source = Excel.Workbook(File.Contents("C:\PBI local files\0025 PQ Reorder Columns\PQ Reorder Columns.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Added Index" = Table.AddIndexColumn(Table1_Table, "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Q1", "Q2", "Q3", "Q4", "Q5"})
in
    #"Reordered Columns"

Later, Excel column headers were renamed and there was a new one. Power Query refresh failed. UI generated M above can’t adapt to column changes.

As Gil says in Chapter 10 for his example:

Obviously, this function will fail to refresh if column names such as Random Column1 and Random Column2 change in the source table.

I stated the challenge something like this:

How can I reorder my Index column to always be the left most column despite any number of unknown columns?

Gil more clearly stated the challenge as:

Can you rewrite this formula to refer to the list of column names above for the reordering step, but somehow only mention “City” and “Revenue” in the code?

…in my case it’s a single column called Index. Now I’ll explain Gil’s solution from the inside out.

Step 1 Table.ColumnNames

Syntax from Microsoft:

Table.ColumnNames(table as table) as list

Returns the column names in the table table as a list of text.

Below, my table = previous step where I added the Index column to original data.

Table.ColumnNames(#"Added Index")

This gives me a list of all column header names whatever they may be.

Step 2 List.Difference

Syntax from Microsoft.

List.Difference(list1 as list, list2 as list, optional equationCriteria as any) as list

Returns the items in list list1 that do not appear in list list2. Duplicate values are supported. An optional equation criteria value, equationCriteria, can be specified to control equality testing.

Below, all column headers except columns I specify inside { }. For me, all but Index.

List.Difference(Table.ColumnNames(#"Added Index"), {"Index"})20

Step 3 List.InsertRange

Syntax from Microsoft:

List.InsertRange(list as list, index as number, values as list) as list

Returns a new list produced by inserting the values in values into list at index. The first position in the list is at index 0.

List.InsertRange(List.Difference(Table.ColumnNames(#"Added Index"), {"Index"}),0,{"Index"})

Within this list of all columns except column Index, add column Index in 1st position (0 = 1st).

Step 4 Table.ReorderColumns

We can’t end with a list. We need a Table function to create the final table.

Syntax from Microsoft:

Table.ReorderColumns(table as table, columnOrder as list, optional missingField as nullable number) as table

Returns a table from the input table, with the columns in the order specified by columnOrder…

Applied to my example:

GilsMagic = Table.ReorderColumns(#"Added Index", List.InsertRange(List.Difference(Table.ColumnNames(#"Added Index"), {"Index"}),0,{"Index"}))

The table is the previous step and the columnOrder is a list we created.

Final M Code

I kept original step “Reordered Columns” as a reference. // makes it a comment.

Step GilsMagic uses four functions.

let
    Source = Excel.Workbook(File.Contents("C:\PBI local files\0025 PQ Reorder Columns\PQ Reorder Columns.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],  
    #"Added Index" = Table.AddIndexColumn(Table1_Table, "Index", 1, 1, Int64.Type),

    //#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Q1", "Q2", "Q3", "Q4", "Q5"})

    GilsMagic = Table.ReorderColumns(#"Added Index", List.InsertRange(List.Difference(Table.ColumnNames(#"Added Index"), {"Index"}),0,{"Index"}))

in
    GilsMagic

Recap

In the real case I pasted/modified Gil’s code to get it to quickly work. I wrote this post to gain a deeper and hopefully more permanent understanding of Gil’s solution. It’s to be expected that Power Query’s UI can’t solve everything. Sometimes a custom M code solution is necessary.

I bought Gil’s book as a birthday present for myself last fall but I had neglected it as I was focusing on DAX. Not anymore! I’m reading it now 🙂

About Gil Raviv

Thanks again to Gil for his solution and book. Gil is a solution architect, is an MVP, and blogs at DataChant,