Unpivot Columns

[Anonymous]

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!

[Anonymous]

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 · Accepted solution]

@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]

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]

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

[amitchandak]

@Anonymous ,All steps in power query

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

First in split by delimited - into columns

then the second column split it by comma  ( ,)  into rows

The create a new column with two new column

column 1=  [Column1] & "-" [Column2]