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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Impactful Individual
Impactful Individual

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
Impactful Individual
Impactful Individual

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors