How to Add Comma Separated Values in Power BI

Dealing with data packed into a single cell, separated by commas, is a common headache in Power BI. This article will walk you through the most effective ways to break apart that data, so you can count, filter, and analyze it correctly using both Power Query and DAX.

Why Split Comma-Separated Values?

Before diving into the "how," let's quickly cover the "why." You might have a column of data that looks something like this, with product tags or survey responses lumped together:

• Product "T-Shirt," Tags "casual, summer, cotton"
• Product "Jacket," Tags "winter, outerwear"
• Product "Shorts," Tags "summer, casual, cotton"

While this looks fine to the human eye, Power BI sees "casual, summer, cotton" as a single, unique text value. If you try to build a simple visual to count how many products have the "casual" tag, you'll run into problems. Power BI will count "casual, summer, cotton" once and "summer, casual, cotton" once, failing to recognize that "casual" appears twice.

Storing multiple values in one cell violates a core principle of data analysis called "tidy data," where each cell should contain only one value. Breaking up these comma-separated strings solves several major issues:

• Accurate Counting & Aggregation: It allows you to correctly count each individual item (like sales per product tag) instead of counting unique combinations.
• Effective Filtering: You can build slicers and filters that let users select individual tags, categories, or keywords. Filtering for "casual" in the example above wouldn't work correctly if the data is combined.
• Building Relationships: Separating values is essential for creating proper data model relationships. You can link a product ID to a separate table of unique categories, improving model performance and analytical flexibility.

By cleaning up this data, you make your reports more accurate, user-friendly, and powerful.

Method 1: The Best Practice - Using "Split Column by Delimiter" in Power Query

For almost every scenario, the Power Query Editor is the best place to handle comma-separated values. It alters the structure of your table before it's even loaded into the Power BI model, which is the most efficient and effective approach. The key is to split the column into new rows, not new columns.

Step 1: Open the Power Query Editor

First, you need to access the Power Query Editor. From the main Power BI Desktop window, go to the Home tab on the ribbon and click on Transform data. This will launch a new window where all data transformations happen.

Step 2: Select the Column You Want to Split

In the Power Query Editor, you'll see a preview of your data tables. Find and click on the header of the column that contains your comma-separated values (e.g., "Tags," "Categories," or "Respondents"). The column will be highlighted.

Step 3: Find "Split Column by Delimiter"

With the column selected, navigate to the Home tab in the Power Query ribbon. Click on Split Column, and then select By Delimiter from the dropdown menu. Alternatively, you can find the same option under the Transform tab.

Step 4: Configure the Split Options (Crucial Step!)

This step is the most important one. A dialog box will pop up with several options.

1. Under "Select or enter delimiter," choose Comma. If the comma is followed by a space, like "tag1, tag2, tag3", you might want to use a custom delimiter ", ", though we will clean trimming spaces later.
2. Leave "Split at" as "Each occurrence of the delimiter."
3. This is the key: A lot of new users miss the Advanced options dropdown. Click to expand it.
4. Under "Split into," select Rows. This is what will transform your data from a wide format to a long, tidy format that is perfect for analysis.

Click OK.

Look at what happens. Power Query takes each item in your comma-separated list and creates a brand-new row for it, duplicating the other values in that original row (like the Product ID or Name). This is exactly what you want.

Before:

After:

Step 5: Trim Whitespace

After splitting by a comma, you often end up with pesky leading spaces (e.g., the value is " summer" instead of "summer"). This can cause issues with your filtering and counting.

To fix this, make sure your newly split column is still selected. Right-click the column header, choose Transform, and then click Trim. This will remove any leading or trailing whitespace from every cell in the column, ensuring your data is clean.

Once you are done, click Close & Apply in the top-left corner of the Power Query Editor to load your newly transformed data into the Power BI model.

Method 2: Using DAX to Sum Numbers in a Comma-Separated String

Sometimes, transforming the table in Power Query isn't an option. Maybe you're working with a data source where you can't alter the structure, or you need to perform a calculation on the fly. Let's say you have a column containing numerical values as text strings, like "15,25,30," and you need to sum them within a measure.

This requires a more advanced DAX approach, but it's very powerful. We will use a combination of SUMX, SUBSTITUTE, PATHLENGTH, and PATHITEM to get the job done.

Imagine your data looks like this:

We want to create a measure called "Total Revenue" that correctly sums the numbers in the RevenueString column.

The DAX Logic Explained

DAX functions related to PATH are designed to work with delimited text, but they require a pipe | delimiter, not a comma. Then, we can iterate through each item in the "path," convert it to a number, and sum it up.

Here is the full DAX measure. Don't worry, we'll break it down piece by piece.

Total Revenue = SUMX( 'YourTable', VAR SubstitutedString = SUBSTITUTE([RevenueString], ",", "|") VAR StringLength = PATHLENGTH(SubstitutedString) RETURN SUMX( GENERATESERIES(1, StringLength, 1), VALUE( PATHITEM(SubstitutedString, [Value]) ) ) )

Breaking Down the Formula

1. VAR SubstitutedString = SUBSTITUTE([RevenueString], ",", "|") First, we create a variable that takes our original string (e.g., "15.50,10,32") and replaces every comma with a pipe character. The result is "15.50|10|32".
2. VAR StringLength = PATHLENGTH(SubstitutedString) Next, we use PATHLENGTH on our newly created string. This function counts how many items are in the pipe-separated list. For "15.50|10|32", this will return 3.
3. GENERATESERIES(1, StringLength, 1) This function creates a temporary single-column table of numbers. It starts at 1, ends at the StringLength (which is 3 in our example), and increments by 1. So it creates a table that looks like this: [1, 2, 3].
4. PATHITEM(SubstitutedString, [Value]) The [Value] here refers to the current number in our generated series (1, then 2, then 3). PATHITEM extracts an item from the pipe-separated string based on its position.
5. SUMX(...) We're using SUMX twice. The outer SUMX iterates over each row in 'YourTable'. The inner SUMX iterates over the GENERATESERIES table (1, 2, 3). For each number in the series, it performs the PATHITEM lookup which is converted to a number with VALUE() — and then adds it all up for each specific OrderID.

While powerful, this DAX method is more performance-intensive than remodeling the data in Power Query. Always default to the Power Query method unless you have a specific reason you need a dynamic DAX measure.

Final Thoughts

Knowing how to handle comma-separated data is a fundamental skill for moving beyond basic Power BI reports. The best practice is almost always to use Power Query's "Split Column by Delimiter" feature to transform your data into a tidy, row-based format. For the less common situations where you need to sum numbers dynamically from a text string, the DAX PATH functions provide a flexible solution.

We know that data preparation in tools like Power BI can sometimes feel tedious, especially with DAX formulas or navigating the transformation steps in Power Query. At Graphed, our goal is to eliminate this friction. We connect your data sources and allow you to build reports and get answers by simply describing what you need in plain English. Instead of learning functions and steps, you can focus on the questions you want to ask, and we'll handle the data wrangling for you.