March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
21 | |
16 | |
13 | |
13 | |
9 |
User | Count |
---|---|
36 | |
31 | |
20 | |
19 | |
17 |