Promote Headers on Selected Columns

[ValeriaBreve]

Hello,

I need to build a table based on an Excel file, where the columns headers need to come from different rows.

Say that for columns 1-10 the columns headers are on row 15, whereas for columns 11-200 the columns headers are on row 16.

How can I reach this? 

Many thanks in advance for your help!

kind regards

Valeria

[ValeriaBreve]

good solution! Thanks I had not thought of it 🙂

[rohit_singh · Accepted solution]

Hi @ValeriaBreve ,

Please try this logic as shown below :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUXLOLKkEUfmleSVFIBYIxepEK0GZOkquuYmZOUA6ICM/LxUsFREZBeQnJiUDyYLCIiBZUVnlkJSnl5yfC+SYWpqamhkbGRspxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"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]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"City", type text}, {"Country", type text}, {"Email", type text}, {"Phone", Int64.Type}})
in
    #"Changed Type1"

Input data 

Transpose the table

Merge the columns with the headers to get a single column

Transpose the table again

Promote headers

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!