Power BI Slicer for Multi-Value Columns: Splitting Delimited Data for Better Filtering

In this article, we will create a slicer based on a data column containing multiple entries separated by a delimiter. The goal is for the slicer to display each entry as an individual item, allowing users to filter data without selecting each grouped entry manually.

As an example, the sample data is an asset inventory with each asset owned by one or more named individuals. In instances where an asset is owned by multiple individuals, their names are delimited by a semicolon. An individual may be an owner of one or more assets.

In our sample data, the individual named “Clark, Ethan” is listed as the singular owner of the assets named “QuickBolt” and “VersaBox” and the co-owner, with “Taylor, Benjamin”, of the asset named “SnapWorks”. If we add a slicer using the Contacts field in our sample data, both entries for “Clark, Ethan” and “Clark, Ethan; Taylor, Benjamin” need to be selected to find all assets owned by “Clark, Ethan”. The goal of this exercise is to create a slicer allowing the user to select “Clark, Ethan” to display all of that owner’s assets regardless of co-ownership.

For this exercise, the source data is in a table named TBL_INVENTORY, with two columns: Asset and Contacts.

Instructions

Preparing the Slicer Data

We need to create a new table with individual entries for the slicer. The query below splits grouped entries using a delimiter. For reference, the full query is provided with step-by-step explanations. Both the query and the resulting table are named TBL_CONTACTS.

let
  Source = TBL_INVENTORY,
  #"Removed Other Columns" = Table.SelectColumns(Source, {"Contacts"}),
  #"Duplicated Column" = Table.DuplicateColumn(
    #"Removed Other Columns",
    "Contacts",
    "Contacts - Copy"
  ),
  #"Split Column by Delimiter" = Table.ExpandListColumn(
    Table.TransformColumns(
      #"Duplicated Column",
      {
        {
          "Contacts - Copy",
          Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ),
    "Contacts - Copy"
  ),
  #"Trimmed Text" = Table.TransformColumns(
    #"Split Column by Delimiter",
    {{"Contacts - Copy", Text.Trim, type text}}
  ),
  #"Removed Duplicates" = Table.Distinct(#"Trimmed Text")
in
  #"Removed Duplicates"

Source Data

We start by creating a blank query:

1. From the Home ribbon in Power BI, click Get data and select Blank query.
2. The Power Query window is displayed.
3. Set the initial data source by entering = TBL_INVENTORY in the command field.

Remove Other Columns

Next, keep only the Contacts column:

1. Highlight the Contacts column.
2. From the Home ribbon, click Remove Columns and select Remove Other Columns.

Duplicate Column

At this stage, only the Contacts column remains.

1. From the Add Column ribbon, click Duplicate Column.
2. We now have two column labeled Contacts and Contacts - Copy.

Split Column

1. Highlight the Contacts - Copy column.
2. From the Transform ribbon, click Split Column and select By Delimiter.
3. The Split Column by Delimiter dialog box is displayed.

4. In our sample data, the delimiter is a semicolon. In the Select or enter delimiter, select Semicolon.
5. For the Split at dropdown, select Each occurrence of the delimiter.
6. Expand the Advanced options.
7. Under Split into, select Rows.
8. Click the OK button.

Trim Text

1. Highlight the Contacts - Copy column.
2. From the Add Column ribbon, click Format and select Trim.

Remove Duplicates

From the Home ribbon, select Remove Rows and click Remove Duplicates.

Relating Tables

Next, we need to create a relationship between TBL_INVENTORY and the newly created TBL_CONTACTS table.

1. In Power BI, switch to the Model view.
2. Create a relationship between TBL_INVENTORY and TBL_CONTACTS by dragging the Contacts field in TBL_INVENTORY to the Contacts field in TBL_CONTACTS.
3. The New relationship dialog box is displayed.
4. Set the Cardinality to Many to many (*:*) with Cross-filter direction set to Both.
5. Enable the Make this relationship active checkbox.
6. Click the Save button.

Add Visualizations and Slicers

Finally, let’s build the report by adding a table visualization and slicer:

1. In Power BI, switch to Report view.
2. Add a Table visualization.
3. From TBL_INVENTORY, configure the table visualization by adding Asset and Contacts to Columns.

4. Add a Slicer.
5. From TBL_CONTACTS, configure the slicer by adding Contacts - Copy to Field.

Results

In the screenshot below, the slicer on the right is based on individual contact names. Selecting “Clark, Ethan” filters the table correctly, displaying all entries associated with him, including those where he is a co-owner.

Summary

In this article, we created a slicer based on a delimited data column, allowing users to filter individual contact names easily. By preparing the slicer data and relating tables, we ensured that selecting a name in the slicer displayed all relevant assets, even those with co-ownership. This approach simplifies filtering and enhances the usability of asset inventory data in Power BI.