How do I program a set of columns to remain leftmost using Power Query?

[Kaitlyn07]

How can I essentially "lock" a set of columns to always be leftmost in my Query table? In my original code, I have them "moved to the beginning" but when new column titles are added to the dataset, it screws up the order.

For example, I want columns A, B and C always to be left-most in the table. Any additional columns can be in any order to the right of these three columns.

Column Headers: A  |   B   |   C  |   x  |   y  |   z   |   etc....

[OwenAuger · Accepted solution]

Sure thing 🙂

The function from the post can be applied, for example, by passing a table as the first argument and the ordered list of columns to be placed at the left as the second argument, i.e.

TableRelativeReorderColumns( SomeTable, {"col1","col2","col3"}

I have attached a small PBIX example.

1. I placed the function code is pasted into a query TableRelativeReorderColumns

let
  Function = 
    (data as table, columnsToOrderLeft as list, optional columnsToOrderRight as list) as table => 
    let
      CurrentOrder = Table.ColumnNames(data),
      ReorderLeft = columnsToOrderLeft,
      ReorderRight = columnsToOrderRight ?? {},
      OrderedColumnsRemoved = List.RemoveItems(CurrentOrder, ReorderLeft & ReorderRight),
      NewOrdering = ReorderLeft & OrderedColumnsRemoved & ReorderRight,
      Reordered = Table.ReorderColumns(data, NewOrdering)
    in
      Reordered,
  FunctionType = 
    type function 
      (
          data as table,
          columnsToOrderLeft as (type {text}), 
          optional columnsToOrderRight as (type {text})
      ) 
      as table
      meta [
        Documentation.Name = "TableRelativeReorderColumns", 
        Documentation.LongDescription = "Returns a table from the input <code>table</code>, with the columns in <code>columnsToOrderLeft</code> appearing leftmost in the order given and the columns in <code>columnsToOrderRight</code> appearing rightmost in the order given. Other columns will not be reordered."
      ],
  Ascribed = Value.ReplaceType(Function, FunctionType)
in
  Ascribed

2. Then created an example query MyTable with the above function applied at the last step, placing columns "A","B","C" in order on the left:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiUyA2A2JzILZQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [x = _t, y = _t, A = _t, B = _t, C = _t, z = _t, p = _t, q = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"x", Int64.Type}, {"y", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"z", Int64.Type}, {"p", Int64.Type}, {"q", Int64.Type}}),
    #"Reorder Columns" = TableRelativeReorderColumns( #"Changed Type", {"A","B","C"} )
in
    #"Reorder Columns"

Note that this ordering will apply in Power Query, but in the data model it will apply only on initial load. To "reset" the ordering to match Power Query, you can unload then reload the table (a messy process). There could be some other method involving external tools as well.

[Kaitlyn07]

Thank you so much, I finally got it to work!

[OwenAuger]

Hi @Kaitlyn07 

Have a look at this post from Ben Gribaudo:

https://bengribaudo.com/blog/2021/10/04/6289/resilient-relative-column-reordering

Specifically, he defines a function that reorders a table's columns by providing lists of the the leftmost or rightmost columns.

One thing to note is that the column order in Power Query is not necessarily replicated in the data model once loaded (comment on above post).

Regards

[Kaitlyn07]

Thank you for the reference. I have entered the first set of code as a separate query but I am not entirely sure how to write my specific data into the code for the column order to be referenced.