Power Query Promote Headers Dynamically

[Anonymous]

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

[Anonymous]

@Vijay_A_Verma You are a Lifesaver!, Thank you very much.. 

[Anonymous]

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 · Accepted solution]

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"