dealing with column which has mulitple values

[Anonymous]

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

[audreygerred · Accepted solution]

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.

[Anonymous]

thank you for you reply just i need to know once i do the step ( unpivot other colum ) on which column shoud i select ?

[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"

[Anonymous]

god bless you it works with me

[audreygerred]

You are very welcome! Happy to help!