Reorder columns in Query Editor without listing the column names

[majdkaid22]

Hi guys,

am doing union dax for 10 tables. While the process is easy, my columns in each table are not in the same order. 

In power Query, all the columns in 10 tables are in the exact same order and excat same name for each column, but when am loading the data PBI Desktop is making the order in in the table in a random way, which is make it impossible to do union. 

Is there a fix or do I have to write a particular M query?

Thanks,

Majd

[zuludogm]

There may have been a change to power query, but danextian's (excellent) solution would now be:

=Table.ReorderColumns( NameOfPreviousStep, List.Sort( Table.ColumnNames( NameOfPreviousStep ), Order.Ascending ) )

[Anonymous]

I get Expression.SyntaxError: Token Eof expected when using this code

[zuludogm]

Hey - perhaps I made a copy/paste error, but here is exactly how I have applied it today:

= Table.ReorderColumns( #"Renamed Columns", List.Sort( Table.ColumnNames( #"Renamed Columns" ), Order.Ascending ) )

When I used the the original from danextian, I also for a syntax/EOF error because it is missing a closing parenthesis, so in my version, I added it. Also, as indicated, I changed List.Order to List.Sort.

[Anonymous]

Brilliant, very much thanks

Regards,

F.

[danextian · Accepted solution]

hi @majdkaid22,

You may reorder your columns in power query alphabetically using a custom code.

=Table.ReorderColumns( NameOfPreviousStep, List.Order( Table.ColumnNames( NameOfPreviousStep ), Order.Ascending )

[Anonymous]

In the Power Query editor for excel, the name of the function is List.Sort. I assume it's the exact equivalent because it worked fine, I didn't even check the arguments.

[Anonymous]

This got me pointed in the right direction, maybe things have changed with time, but I had to make some adjustments to above for it to work:

Table.ReorderColumns(PreviousStep, List.Sort(Table.ColumnNames(PreviousStep),Order.Ascending))

[Ssibbett]

I like this subject. Is there a way to do this but omiting the first 4 columns? 

First 4 columns would remain as: "Item Name","Package Type","Attribute","Item No"

[Anonymous]

Yes, first create a list of the columns not to be sorted.

Then use List.RemoveItems to remove this list from the rest of the Table.ColumnNames items - this creates a list of columns to be sorted.

Then use List.Sort to sort those.

and then use List.Combine to Combine the not to be sorted columns and the sorted columns. (FinalOrder)

Last step:

Table.ReorderColumns(PreviousTable, FinalOrder)

[Tvineyy]

@Anonymous -  please can you expand with an example?

I have a table with column headers "email", "name", "Address", followed by 3 headers that always change with the dataset - question 1, question 2 and question 3.

When I format the first headers it automatically moves them to the end.

How I can I reorder them back to the order above without explicitly mentioning the question 1-3 headers as these aren't defined  -they change everytime.

[newts]

Know this is old but hope it helps someone:

Where the previous step is Output and the column you want first is "Employee Number"

Reordered= Table.ReorderColumns(Output,List.Combine({{"Employee Number"},List.RemoveItems(Table.ColumnNames(Output),{"Employee Number"})}))

[majdkaid22]

@danextian @v-jiascu-msft thanks gents. this solution worked!

[v-jiascu-msft]

@majdkaid22

Hi Majd,

What kind of union do you want to do? If it's "Merge Queries", the order won't be a problem. If it's "Append Queries", Power BI will take it in a intelligent way according to my test. (Maybe it could go wrong). Please have a try.

Are the tables Excel tables? If so, please make sure they are all tables, not ranges. 

Best Regards!

Dale

[v-jiascu-msft]

Hi @majdkaid22,

Could you please tell me if your problem was resolved? Please mark the proper answer if it worked. That will be a help to the others.

Best Regards!
Dale