Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to convert double headers in columns

Dear all,  The file from the data base comes with the date on the 1st row than the fact in 2nd row. The first column is the Exchange rate level and the others columns are the values for each fact by...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Anonymous ,

     

    Please use Transpose and Merge column function in the query editor.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ11DVT0kEwY3WilUKDXYBCviG+JfkliTlgISM9U6CQqZmpqSlEjbEeWJsxCIIFTPTMQQJmQDWWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", 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",{{"2021-1-6|USD", type number}, {"2021-1-6|MTMtotal", Int64.Type}})
in
    #"Changed Type1"

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.