Moving columns in PowerQuery without naming every column

If you have pivoted data, you probably don’t want to name all the columns to move one, as a lot of them are going to contain dynamic data. The solution is to use the list of columns, manually moving the one you want into the desired position, as such:

= Table.ReorderColumns(PreviousStepName, List.InsertRange(List.RemoveItems(Table.ColumnNames(PreviousStepName), {"ColumnToMove"}), 4, {"ColumnToMove"}))

This moves the column “ColumnToMove” to position 4, by removing it from the list of columns in the previous step, and then re-inserting it at position 4.

This can also be used directly when merging two tables, putting the other table at a specific position:

= Table.ReorderColumns(Table.NestedJoin(PreviousStepName, {"LookupColumn"}, TargetTable, {"TargetLookupColumn"}, "Table", JoinKind.LeftOuter), List.InsertRange(List.RemoveItems(Table.ColumnNames(PreviousStepName), {"Table"}), 4, {"Table"}))

This joins the PreviousStepName step with the TargetTable (a step or a different query), by looking up the column “LookupColumn” in TargetTable[TargetLookupColumn], saving the join column as “Table” at position 4.

Expanding on this, you can even expand one or more columns from another table, directly into a given position:

= Table.ExpandTableColumn(Table.ReorderColumns(Table.NestedJoin(PreviousStepName, {"LookupColumn"}, TargetTable, {"TargetLookupColumn"}, "Table", JoinKind.LeftOuter), List.InsertRange(List.RemoveItems(Table.ColumnNames(PreviousStepName), {"Table"}), 4, {"Table"})), "Table", {"TargetTableColumnName"}, {"DesiredColumnName"})

The new items here are TargetTableColumnName, which must exist in the table you’re merging with, and the DesiredColumnName, which is the name you’ll end up with. This can, of course, be the same as the TargetTableColumnName if you want.

As a bonus, if the join returns several matches per lookup, you can expand the column as a List like this:

= Table.TransformColumns(PreviousStepName, {"Table", each _[Item]})

This targets the joined “Table” column we created in the previous query, extracting the matching “Item” column entries from TargetTable (see previous query) as a list, instead of multiplying your rows, as Table.ExpandTableColumn() would.

Have fun?

Leave a Reply

Your email address will not be published.