Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Unpivot Columns

Hello, I want to get the result depicted in the bottom half of the picture from the data which is arranged in the format shown in the photo above.  Thank you for helping me out!
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Use this code in blank query in power query

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMUtJRcnRyBpJBvsG6hjpGOsY6JkqxOtFKAYFBQFFPb18kOVOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column3", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column3.1", "Column3.2"}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Column3.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column3.2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column3.1", type text}, {"Column3.2", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column3.2", type text}}, "en-US"),{"Column3.1", "Column3.2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged")
in
    #"Merged Columns"