Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
50 | |
19 | |
12 | |
11 |