Parsing comma separated values for slicer

[TimQ]

I have a field text field called "Committee" that denotes a committee number for each record. A record can have more than one committee number associated with it, which is separated by commas, i.e., below:

Record ID |  Committee

1               |   1, 2, 4

2               |   1

3               |   3, 4, 12

I want to create a slicer that will return the records with a selected committee number, which ranges from 1 to 32. Using the example data above, a selected slicer value of "1" would return records 1 and 2, but not 3. What's the simplest way to do this? 

Thanks.

[dax]

Hi TimQ, 

Yes, you could refer to above suggestions to modift table structure like below(use below M query in edit queries)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUMdIxUYrViVYyAvHALGMgy1jHRMfQSCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Committee = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Committee", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Committee.1", "Committee.2", "Committee.3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "slicer"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"slicer", Int64.Type}})
in
    #"Changed Type"

Then you could use slicer field in Slicer, when you choose 1, it show  ID, 1 AND 2 in table

Best Regards,
Zoe Zhi

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

[HotChilli · Accepted solution]

Unless there is a good reason for storing the data that way, I think you should use Power Query to split the Committee column into rows.

Use 'Split column'-> by delimiter (a comma) and choose 'Into rows' from the advanced section.

That will get you a more 'relational' table.  You can then use Committee in a slicer