Forum Discussion

TimQ's avatar
TimQ
Icon for Helper I rankHelper I
7 years ago
Solved

Parsing comma separated values for slicer

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.

Need Help
  • HotChilli's avatar
    HotChilli
    7 years ago

    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

2 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    7 years ago

    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.