Forum Discussion
dealing with column which has mulitple values
hi guys
i am facing a problem in powerbi
see me data
when i use slicer in powerbi it shows me the excat values under cars field see below
actually i need my slicer to has unique values like
Toyota
Mazda
Ford
Nissan
GMC
and based on my selection it will impact the customer name
i was thinking for a way to do that but actually i didnt get any result
is there any way to solve the above problem without spliting the column with delimeters? because splitting the data it will not fix the issue up to my knowledge
Hello! In Power Query you are going to want to split by delimeter, then unpivot other columns. This way you wind up with a table that looks like this:
Instead of this:
With it in the new format, you can have your slicer do what you wanted:
If I click Mazda, we can see jean and john appear.
5 Replies
- audreygerred
Super User
Hello! In Power Query you are going to want to split by delimeter, then unpivot other columns. This way you wind up with a table that looks like this:
Instead of this:
With it in the new format, you can have your slicer do what you wanted:
If I click Mazda, we can see jean and john appear.
- audreygerred
I was on the name column when I did that step. Here is the M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykpMzlbSUQrJr8wvSdTxyywuTszTcc5ILSvKz0ktUYrVASrJz8gDKvFNrEpJ1IEohIgn5qYWAyWgmtx9nXXc8otSoHLF+SBNCIHURLghYIGiTLDFcKvgJscCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Cars = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Cars", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Cars", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Cars.1", "Cars.2", "Cars.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Cars.1", type text}, {"Cars.2", type text}, {"Cars.3", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Name"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
#"Removed Columns"