Slicer for multi cell value

[Syndicate_Admin]

I have columns which is kind of  following cell value at different cells.

r1,r2,r3

r1,r2

r1

r2

r2,r3,r4

i was trying to create slicer selection of unique value from above column likes of r1 r2 r3

[Syndicate_Admin]

Are you saying that "r1, r2, r3" are all contained in one field?

In Power Query, do a Split Column by Delimiter, and under Advanced, select "Rows"

Then Remove Duplicates to get to only ONE row of "r1"

[Syndicate_Admin]

Hi @Vishwamber 

Do you describe the sample data as shown in the figure below.

If the sample data is like the picture above, you can put the following code to Advanced Editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjJU0lEqMgIRxkqxOsgCCC6cY4TOAWoCEiZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Duplicates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Thanks for this however I need to use same for later filter for eg if I select r1 it will filter all value for row which contain r1.

TC	Status	plat
tc123	pass	r1,r2
tc124	fail	r1,r3,r5
tc125	fail	r1,r4,r3,r7
tc126	pass	r2,r5,r2,r3
tc127	fail	r1,r6
tc128	fail	r5
tc128	fail	r1

If I choose r1 it will filter all 

TC	Status	plat
tc123	pass	r1,r2
tc124	fail	r1,r3,r5
tc125	fail	r1,r4,r3,r7
tc127	fail	r1,r6
tc128	fail	r1

[Syndicate_Admin]

Hi @Vishwamber 

You can refer to the following solution.

Step 1: You can create two blank query, then put the following code to advanced editor in power query then apply them.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkk2NDJW0lEqSCwuBlJFhjpFRkqxOhAJE6BIWmJmDlTCWKfIFC5niipnApY2h0ubIZlpBNSnAyKN4dLmqLrN4BIWSBKmWEUNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TC = _t, Status = _t, plat = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TC", type text}, {"Status", type text}, {"plat", type text}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkk2NDJW0lEqSCwuBlJFhjpFRkqxOhAJE6BIWmJmDlTCWKfIFC5niipnApY2h0ubIZlpBNSnAyKN4dLmqLrN4BIWSBKmWEUNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TC = _t, Status = _t, plat = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TC", type text}, {"Status", type text}, {"plat", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "plat", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"plat.1", "plat.2", "plat.3", "plat.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"plat.1", type text}, {"plat.2", type text}, {"plat.3", type text}, {"plat.4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"TC", "Status"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"TC", "Status", "Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Step 2: put the value of query(2) to slicer.

then create a measure

Measure = IF(COUNTROWS(FILTER(VALUES('Query1 (2)'[Value]),CONTAINSSTRING(SELECTEDVALUE(Query1[plat]),[Value])))>0,1,0)

Then put the measure to the table visual filter

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

On the selection of r1 my pi chart only will show the satus of all r1. Need to link same.

[Syndicate_Admin]

Hi @Vishwamber 

You can just modified the measure

Measure = IF(COUNTROWS(FILTER(VALUES('Query1 (2)'[Value]),CONTAINSSTRING(SELECTEDVALUE(Query1[plat]),[Value])))>0,CALCULATE(COUNTA(Query1[TC])),0)

Then create a new measure and put the measure to the table visual filter and put the new measure to the pie chart 

Measure1 = SUMX(Query1,[Measure])

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Thanks a lot Yolo Zhu.