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"

     

     

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 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous,

      I check the amitchandak's sample and find it should work with the sample data you shared. It split columns based on delimiter "-" to split the RSM and detail values from your text column, then split the detail values to list to expand row based on commas and merge the RSM and value to achieve your requirement.

      As you said, these codes do not work on your side. Is there any special on your records so that the code does not work? How to Get Your Question Answered Quickly

      Regards,

      Xiaoxin Sheng