Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
robertobrsp
Frequent Visitor

How to Reorder Columns in Different Sections of a Consolidated

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 colunaBcolunaC
itemAitemBitemC
itemAitemBitemC
itemAitemBitemC
itemAitemBitemC
colunaBcolunaCcolunaA 
itemBitemCitemA
itemBitemCitemA
itemBitemCitemA
itemBitemCitemA
colunaCcolunaA colunaB
itemCitemAitemB
itemCitemAitemB
itemCitemAitemB
1 ACCEPTED SOLUTION
Omid_Motamedise
Super User
Super User

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"

 

 

 

View solution in original post

4 REPLIES 4
Omid_Motamedise
Super User
Super User

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
Super User
Super User

Hi @robertobrsp, another solution:

 

Output

dufoq3_0-1725570849921.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

ronrsnfld
Super User
Super User

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

ronrsnfld_0-1725543985052.png

 

Results

ronrsnfld_0-1725541604479.png

 

 

 

m_dekorte
Super User
Super User

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...

m_dekorte_0-1725541210382.png

 

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

 



Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.