Sort Column Name in Power Query via M Code

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.

Power Query

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.

Create Function

If you use this feature a lot, you can create a function and invoke it. see how to create a custom function

let
SortColumn=(tablename as table) as table=>Table.ReorderColumns(tablename, List.Sort(Table.ColumnNames(tablename)))
in
SortColumn

see related Video

Leila Etaati

Trainer, Consultant, Mentor

Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.