Reordering column using a use define table

[DanFromMontreal]

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?

[Vijay_A_Verma · Accepted solution]

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]))

[Vijay_A_Verma]

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]))

[DanFromMontreal]

@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. 

[Anonymous]

---

@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?

[Anonymous]

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
    trc

[DanFromMontreal]

@Anonymous , 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.

[Vijay_A_Verma · Accepted solution]

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]))

[DanFromMontreal · Accepted solution]

Great stuff.

Simple and to the point.

Many thanks