Reorder query columns without listing all columns names

[jeongkim]

Hi,

When I reorder particular column, all the columns are listed in code so it will get error any other column name changes in the future. 

Can we reorder with just order place number? like renaming as below:

= Table.RenameColumns(#"Promoted Headers",
{{Table.ColumnNames(#"Promoted Headers"){0}, "Source Name"},
{Table.ColumnNames(#"Promoted Headers"){1}, "Date Created"},

[v-kpoloju-msft · Accepted solution]

Hi @jeongkim,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @kushanNa, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.     You should take the sample data form excel file.

Before changing columns reorder:

M query for Changing columns reorder:

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-sathmakuri\OneDrive - Quadrant Resource LLC\Test.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    RenamedCols = Table.RenameColumns(#"Promoted Headers", {
        {Table.ColumnNames(#"Promoted Headers"){0}, "Source ID"},
        {Table.ColumnNames(#"Promoted Headers"){1}, "Source Name"},
        {Table.ColumnNames(#"Promoted Headers"){2}, "Date Created"},
        {Table.ColumnNames(#"Promoted Headers"){3}, "State"},
        {Table.ColumnNames(#"Promoted Headers"){4}, "Date updated"}
    }),
    Cols = List.RemoveItems(Table.ColumnNames(RenamedCols), {"State"}),
    ReorderedCols = List.InsertRange(Cols, 1, {"State"}),
    Final = Table.ReorderColumns(RenamedCols, ReorderedCols)





in
   Final

 After changing the columns reorder:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[jeongkim]

Thanks, let me try,

so number 1 means 'state' column will be inserted 2nd column and existing 2nd column will be moved to right to 3rd column right?

  ReorderedCols = List.InsertRange(Cols, 1, {"State"}),

result will be:

columnA columnB columnC columnD

columnA state columnB columnC columnD  

right?

[v-kpoloju-msft]

Hi @jeongkim,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[kushanNa]

add the dynamicColumn (assume this is the state column) variable to the 6th position ? in the above example it's in the 2nd 

[jeongkim]

That one is chainging name which is already in code mine, not reordering..

My query is how to dynamically reorder.  

[kushanNa]

Hi @jeongkim 

Since your step is not happening right after the Promoted Headers step you will need to create a variable for this and pass that variable 

eg:

 #"Promoted Headers" = Table.PromoteHeaders(Source),
    dynamicColumn = Table.ColumnNames(#"Promoted Headers"){2},
    #"Add State" = ...,  // whatever your "add state" step is
    #"Reordered Columns" = Table.ReorderColumns(#"Add State", {"order id", dynamicColumn, "customer name"})

[jeongkim]

e.g. If I wanna reorder column 'state' to 6th column order from left, how can I write the code pls?