Sometimes people ask questions about how to order columns in Power query, mostly for Queries with Lots of Columns.
We can reorder column Name with Drag and Drop or use Move in GUI to change the column’s order, but it is also possible to reorder via code.
In this post, I will show how to Order Column Names in Power Query using M Code.
I have a DimCustomer Table from AdbentureworksDW2012.
In this table, I have 30 Columns. I want to Sort Column Names alphabetically.
To This aim, select the Advance Editor to access the M code.
Inside the last part before “in”
I defined a new variable, Sortcol; in this variable, I am going to use Function Table.ReorderColumns, This function helps me to Reorder the column in Power Query; for the input for this function, I will pass my table name and the Column Order
for the first Parameters. I will pass the #”Change Type” as the previous step that holds the Table data.
For the second Parameter, I need a list of the column; instead of passing the 30 columns’ names, I first use a function that returns the Sorted list; this function gets a list of names and then sorts them alphabetically. That means If I pass the column Name to this function, it first sorts them and returns them as an alphabetically sorted list. That can be useful for my Reodercolumn function.
As a result, I need to pass the list of column names to the List. Sort function
Sortcol = Table.ReorderColumns(#"Changed Type", List.Sort(Table.ColumnNames(#"Changed Type")))
So I use three functions:
1-Table.ReorderColumns() for reordering the column
2- List.Sort(): to sort the column Name alphabetically
3-Table.ColumnNames(): To fetch the column Name
SO the result would be as below
The initial state
The final State
Now I am going to duplicate on the Birthdate column; when you duplicate a column here, by default, the Power query added this one to the end of the column List.
Now I just change the order of steps in the applied Steps to Sort the Columns again.
If you use this feature a lot, you can create a function and invoke it. see how to create a custom function
SortColumn=(tablename as table) as table=>Table.ReorderColumns(tablename, List.Sort(Table.ColumnNames(tablename)))
see related Video