Forum Discussion

majdkaid22's avatar
majdkaid22
Helper V
9 years ago
Solved

Reorder columns in Query Editor without listing the column names

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

 

 

Need Help
  • danextian's avatar
    danextian
    9 years ago

    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 )

     

14 Replies

  • danextian's avatar
    danextian
    Super User
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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))
  • zuludogm's avatar
    zuludogm
    Advocate II
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • zuludogm's avatar
        zuludogm
        Advocate II
        5 years ago

        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.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      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