Forum Discussion
Reorder query columns without listing all columns names
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"},
Hi Anonymous,
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 FinalAfter 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.
7 Replies
- kushanNa
Super User
Hi Anonymous
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"}) - kushanNa
add the dynamicColumn (assume this is the state column) variable to the 6th position ? in the above example it's in the 2nd
v-kpoloju-msftCommunity Support
Hi Anonymous,
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 FinalAfter 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.- v-kpoloju-msft
Hi Anonymous,
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.
