John Dalesandro

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

By John Dalesandro

Published

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.

Baseline Report
Baseline Report

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.

Unexpected Slicer Result: Missing Co-owned Entries
Unexpected Slicer Result: Missing Co-owned Entries

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

Inventory Table
Inventory Table

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.
Query Source Data
Query Source Data

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.
Remove Other Columns
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.
Duplicate Column
Duplicate Column

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.
Split Column By Delimiter Configuration
Split Column By Delimiter Configuration
  1. In our sample data, the delimiter is a semicolon. In the Select or enter delimiter, select Semicolon.
  2. For the Split at dropdown, select Each occurrence of the delimiter.
  3. Expand the Advanced options.
  4. Under Split into, select Rows.
  5. Click the OK button.
Split Column By Delimiter
Split Column By Delimiter

Trim Text

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

Remove Duplicates

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

Remove Duplicates
Remove Duplicates

Relating Tables

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

Model View
Model View
  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.
New Relationship Configuration
New Relationship Configuration

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.
Table Visualization Configuration
Table Visualization Configuration
  1. Add a Slicer.
  2. From TBL_CONTACTS, configure the slicer by adding Contacts - Copy to Field.
Non-delimited Slicer Configuration
Non-delimited Slicer Configuration

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.

Expected Result
Expected Result

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.