Forum Discussion
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...
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"
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
Most Valuable Professional
Most Valuable ProfessionalSolution 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"