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"

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello, 
    Thank you for your reply. In that case, I'd still have 

    RSM-1

    2

    3


    While I need

    RSM-1

    RSM-2

    RSM-3 and so on. 

    Can you suggest how I can do that?

    Also, if I choose comma as my delimiter, then I don't think '1' would be captured and the table will start from 2, right?

     

    • amitchandak's avatar
      amitchandak
      Super User
      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"

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hey, can you explain the formula?

        I am not able to get the required result. 

        Is there a simpler way to do it? It is fine if the new method is lengthy. 

         

        Thanks again 🙂