Define column order with varying number of columns

[skean21]

Dear community,

I'm facing the following problem:

I want the columns in a table to appear in a defined order, the problem is that not all columns exist always. When I apply the sort order command I can sort the columns - the problem is that as soon as one column in the command doesn't exist, Power Query displays an error message.

For example my table has these columns:

ColumnA

ColumnB

ColumnC

ColumnD

ColumnE

I want them to appear in the order of this table (TableA):

Column       Position

ColumnB	1
ColumnA	2
ColumnD	3
ColumnE	4
ColumnC	5

Now it would be possible, that for example column D doesn't exist, so ColumnE should be at position 3.

The sorting order I want I could define it in a table like table A.

Can anybody help me how to realize this in Power Query?

Thanks in advance,

Regards

Felix

[AlB · Accepted solution]

Hi @skean21 

Assuming your TableA is sorted (as you show it): 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcKxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t, ColumnC = _t, ColumnE = _t]),

    #"Reordered Columns" = Table.ReorderColumns(Source,List.Intersect({TableA[Column],Table.ColumnNames(Source)}))
in
    #"Reordered Columns"

Copy the code above in a blank query to see the steps

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[skean21]

@AlB thanks a lot, very helpful!