Forum Discussion
TootsiePatootsi
8 years agoRegular Visitor
CSV values
Hi, First of all thanks for reading this. I am new to PowerBI so I am hoping you could help me out. I have the following data # Column Values Request #1 A,B,C Request #2 C,A ...
dramus
8 years agoContinued Contributor
Split and Unpivot is your friend.
First split your "Column values" using a comma as the delimiter.
Then select the columns you just created and select unpivot.
Finally delete the "Attribute" column that was created.
You will end up with something that looks like this:
Here's the Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotLE0tLlFQNlTSUXLUcdJxVorVQRI2Ago76zgqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Requests = _t, #"Column Values" = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column Values", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column Values.1", "Column Values.2", "Column Values.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Requests", type text}, {"Column Values.1", type text}, {"Column Values.2", type text}, {"Column Values.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Requests"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
#"Removed Columns"
TootsiePatootsi
8 years agoRegular Visitor
Hi,
Thanks for the reply. I am not familiar with PowerQuery yet and also Unpivot ill see if this could help and let you know