Forum Discussion
Reordering column using a use define table
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
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]))Great stuff.
Simple and to the point.
Many thanks
let Source = Table.FromRecords({[CustomerID = 1, Phone = "123-4567", Name = "Bob"]}), colnames=Table.ColumnNames(Source), neworder={"Phone", "Name"}, rest=List.Difference(colnames, neworder), trc = Table.ReorderColumns(Source, neworder&{"qty"}&rest, MissingField.UseNull) in trcAnonymous , thank you for your response but Vijay's solution meet all requirement.
His code made reference to my tblHeaderSequence and was applicable immediately, regardless of which column his missing.
DanFromMontreal wrote:
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?
the solution I proposed should solve the problem of the missing columnS.
Have you tried it?