Forum Discussion
CSV values
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"
- TootsiePatootsi8 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 - TootsiePatootsi8 years agoRegular Visitor
Hi Dramus,
I tried the unpivot feature but somehow all my columns shows error and showing this error
We couldn't parse OData response result. Error: A value without a type name was found and no expected type is available. When the model is specified, each value in the payload must have a type which can be either specified in the payload, explicitly by the caller or implicitly inferred from the parent value
Thanks in advance
- parry2k8 years agoSuper User
TootsiePatootsi columns will be duplicate once your split it to rows and that is the only way you can get what you are
looking for.
- dramus8 years agoContinued Contributor
Which columns did you have selected when you tried to unpivot?
It should look like this before you do the unpivot:
Make sure all three of the "Column Values" columns are selected and then Right click on one of the headers and select "Unpivot Columns"
- dramus8 years agoContinued Contributor
Or...You can right click the "#"/Requests column, and select the "Unpivot Other Colu,mns" option. Same result.