Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
How can I reorder columns in different sections of a consolidated table in Power Query to match the order of the first section? The dataset contains multiple sections with the same data but different column orders, and I need to make all sections follow the order of the first part.
Example
colunaA | colunaB | colunaC |
itemA | itemB | itemC |
itemA | itemB | itemC |
itemA | itemB | itemC |
itemA | itemB | itemC |
colunaB | colunaC | colunaA |
itemB | itemC | itemA |
itemB | itemC | itemA |
itemB | itemC | itemA |
itemB | itemC | itemA |
colunaC | colunaA | colunaB |
itemC | itemA | itemB |
itemC | itemA | itemB |
itemC | itemA | itemB |
Solved! Go to Solution.
use this code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Grouped Rows" = Table.Group(#"Demoted Headers", {"Column1"}, {{"Count", each Table.PromoteHeaders(_)}},0,(x,y)=> Number.From(Text.StartsWith(y[Column1],"coluna"))),
#"Removed Top Rows" = Table.Skip(#"Grouped Rows",1),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Top Rows", "Count", {"colunaA ", "colunaB", "colunaC"}, {"colunaA ", "colunaB", "colunaC"})
in
#"Expanded Count"
use this code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Grouped Rows" = Table.Group(#"Demoted Headers", {"Column1"}, {{"Count", each Table.PromoteHeaders(_)}},0,(x,y)=> Number.From(Text.StartsWith(y[Column1],"coluna"))),
#"Removed Top Rows" = Table.Skip(#"Grouped Rows",1),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Top Rows", "Count", {"colunaA ", "colunaB", "colunaC"}, {"colunaA ", "colunaB", "colunaC"})
in
#"Expanded Count"
Hi @robertobrsp, another solution:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyixJzXVU0gHTTlDaWSlWh9oyyfk5pXmJIDEIyxnOclSAa0Vo0YEaRX0ZLNbrwF0H04rQAvMLWTKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"colunaA " = _t, colunaB = _t, colunaC = _t]),
CorrectedColumnOrder = [ FirstColumn = Table.Column(Source, Table.ColumnNames(Source){0}),
lst = List.Select(FirstColumn, (x)=> Text.StartsWith(x, "coluna", Comparer.OrdinalIgnoreCase)),
pos = List.PositionOfAny(FirstColumn, lst, Occurrence.All),
posZip = List.Zip({ pos, List.RemoveFirstN(pos, 1) & {null} }),
transformed = List.Transform(posZip, (x)=>
[ a = Table.Range(Source, x{0}, x{1} - x{0}),
b = Table.PromoteHeaders(a),
c = Table.ReorderColumns(b, Table.ColumnNames(Source))
][c]
),
combined = Table.Combine({Table.FirstN(Source, pos{0})} & transformed)
][combined]
in
CorrectedColumnOrder
Here is one way to accomplish that.
Please read the code comments to better understand the algorithm as well as possible pitfalls if your example is not truly representative.
If the first set does not include all of the possible column headers, minor changes will be needed.
Also note that I did not set the column data types and this may be important to do subsequently.
let
//ensure first row of data are NOT the column headers, but rather they are {"Column1","Column2",...}
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7PKc1LdFRQ0oEyneAsZ6VYnWilzJLUXEegGIh2gtK0kMG0XQfhOJhWhBYdqFHUl8FiPSJsYFoRWmB+IUsmFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
//Group by the column names rows
//then promote headers within each subgroup
//this method requires some common naming scheme for the column headers
// if this is not the case, provide a more realistic example
#"Grouped Rows" = Table.Group(Source, {"Column1"}, {
{"all", each Table.PromoteHeaders(_,[PromoteAllScalars=true])}},
GroupKind.Local,(x,y)=>Number.From(Text.StartsWith(y[Column1],"coluna"))),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Column1"}),
#"Expanded all" = Table.ExpandTableColumn(#"Removed Columns", "all", {"colunaA ", "colunaB", "colunaC"}, {"colunaA ", "colunaB", "colunaC"})
in
#"Expanded all"
Posted Data
Results
Hi @robertobrsp,
As a best practice make sure to have your column names promoted before you combine data/tables.
That way there is nothing to fix later, as it will automatically stack like named columns on top of each other.
Try it yourself, split your sample into three separate Queries, make sure headers are promoted before you combine them. It's like magic...
Alternatively, you could try this.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyixJzXVU0gHTTlDaWSlWh9oyyfk5pXmJIDEIyxnOclSAa0Vo0YEaRX0ZLNbrwF0H04rQAvMLWTKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"colunaA " = _t, colunaB = _t, colunaC = _t]),
ListOfRows = Table.ToRows(Table.DemoteHeaders(Source)),
HeaderIndices = List.Select(List.Positions(ListOfRows), each Text.StartsWith(ListOfRows{_}{1}, "coluna")),
SplitTable = (temp as list, indices as list) =>
List.Transform(
List.Transform(List.Zip({indices, List.Skip(indices)}), each List.Range(temp, _{0}, _{1} - _{0})),
each Table.PromoteHeaders(Table.FromRows(_))
),
StartIndices = List.Combine({{0}, HeaderIndices}),
Result = Table.Combine(SplitTable(ListOfRows, StartIndices))
in
Result
I hope this is helpful
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
55 | |
43 | |
28 | |
22 |