Forum Discussion
Reordering column using a use define table
Hello dear Power query community,
When I'm importing data from a CSV file and my 6 column header name are sequenced as shown in E3:J3
My final output should be like E5:J5
I have rearrange my columns manually but since I'm learning the PoweM language, I'm sure there is a better way of doing this.
I thought of a table defining the sequence.. This could come handy if my next file is containing more columns (>20).
for the column name that ARE NOT defined in the table, they should be place at the far right side
Any idea?
Yes, that column is redundant. Use this.
= Table.ReorderColumns(Source,List.Select(tblHeaderSequence[Header], (i)=> List.Contains(Table.ColumnNames(Source),i))&List.Difference(Table.ColumnNames(Source),tblHeaderSequence[Header]))Great stuff.
Simple and to the point.
Many thanks
7 Replies
I have assumed that second column you will use to sort the first column in tblHeaderSequence
Use below formula to reorder your columns.
= Table.ReorderColumns(Source,tblHeaderSequence[Header]&List.Difference(Table.ColumnNames(Source),tblHeaderSequence[Header]))Vijay_A_Verma , worked almost like a charm.
If the column "Qty" was NOT present, I get an error. Any way to overcome this without having to manually add the column?
Also, I noticed that you do not reference to the column "Column" to get the ordering. I therefore assume that it is irrelevant and the order is set by where the column name is in the list.
Yes, that column is redundant. Use this.
= Table.ReorderColumns(Source,List.Select(tblHeaderSequence[Header], (i)=> List.Contains(Table.ColumnNames(Source),i))&List.Difference(Table.ColumnNames(Source),tblHeaderSequence[Header]))