Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
4 years ago
Solved

Promote Headers on Selected Columns

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

Need Help
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    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!

     

     

2 Replies

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    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!