How to Reorder Columns in Different Sections of a Consolidated

[robertobrsp]

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

[Omid_Motamedise · Accepted 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"

[dufoq3]

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

[ronrsnfld]

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

[m_dekorte]

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