How to convert double headers in columns

[baalmeida]

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 each exchange level (Please see the image bellow). I need to convert this model into 4 columns (Date, Exchange Rate, fact and values), whats is the best way to do it?  

Thanks!

[V-lianl-msft · Accepted solution]

Hi @baalmeida ,

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.

[Ashish_Mathur]

Hi,

See if my solution here helps - Rearrange a multi heading dataset into a single heading one which is Pivot ready.