Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power Query Promote Headers Dynamically

Hi Team,

I have a dataset whose sample is below:


Now i want to Promote the headers of first two rows from first row and Promote the headers of rest rows from 2nd row.

I have almost 100 rows like this in the original dataset so its difficult to update manually.

Please Advise.

Thanks
Amit

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Solution file upoaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuSx85gpq2K8FOskP?e=eBqPq8 

    You can incorporate following M codes

    For case 1

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"

     

    For Case 2

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each (if Text.Contains([Column1],"Column") or Text.Contains([Column1],"_") then "" else [Column1])&(if [Column2]=null then "" else [Column2])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1", "Column2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{List.Last(Table.ColumnNames(#"Removed Columns"))}&List.RemoveLastN(Table.ColumnNames(#"Removed Columns"),1) ),
    #"Transposed Table1" = Table.Transpose(#"Reordered Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I have one more situation where i already have header for the first 2 rows and rest of rows has the header in the first row.

    Please advise for this one as well.

    Thanks

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      Solution file upoaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuSx85gpq2K8FOskP?e=eBqPq8 

      You can incorporate following M codes

      For case 1

       

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"

       

      For Case 2

      let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each (if Text.Contains([Column1],"Column") or Text.Contains([Column1],"_") then "" else [Column1])&(if [Column2]=null then "" else [Column2])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1", "Column2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{List.Last(Table.ColumnNames(#"Removed Columns"))}&List.RemoveLastN(Table.ColumnNames(#"Removed Columns"),1) ),
    #"Transposed Table1" = Table.Transpose(#"Reordered Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"