Forum Discussion
Multiple Values Same Column
Hi, BPelzel
1. Try to split cells into rows with commas and line breaks in PowerQuery.
2. And delete the extra spaces before and after the value.
Data:
Steps:
data:
Then to create a measure to count the fruit.
_count = COUNT('Table'[data])Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, you need to tidy the data up in PowerQuery:
1. Split the first column by delimiter ","
2. Unpivot the new columns.
you will have a table that now has a field called attribute with all the fruits and one with the values.
3. Rename them and load back to PowerBi.
The code will be something like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WStTJV9JRMlWK1QGxk8A8IzAPwjaEyQDZxiiqzKE8ENtEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Value = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Fruit", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Fruit.1", "Fruit.2", "Fruit.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Fruit.1", type text}, {"Fruit.2", type text}, {"Fruit.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Value"}, "Attribute", "Value.1"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Value.1", "Fruit"}})
in
#"Renamed Columns"
Thank you for your quick reply!
Now I'm getting errors about 'Microsoft.Mashup.Evaluator.MessageBasedOutputStream+BinaaryChunkMessage' and that there's duplicate values for our employee ID that are not allowed in a many-to-one relationship.
Also, I'm getting hung up on step 3. Now there are two columns at the end of the existing table for Attribute and Value. It appears that there's only one value (or null) in that Value column. How should I use those to create a table? Once that happens, will they go away to remove the error about duplicate employee ID's?
