Power BI/Query: How to Show Multi-Value Column as Single Row and Filter on Individual Items?

[dBlackstock]

Hi everyone,

I’m pulling data from a Microsoft List into Power BI. One of the columns contains multiple values (like a collection of tags or items), and Power BI is creating a separate row for each of those values—so I end up with a lot of duplicate rows. From MS Lists:

What I’ve tried:

1. Extract to new column with line breaks:

In Power Query, I extracted the values to a new column and separated them using “Replace Values” to put each value on a new line. This keeps one row per person, but on the dashboard, Power BI concatenates all the values together, so I can’t filter on individual items. Here’s how it looks in the dashboard, slicer, etc:

2. Extract to new rows:

I also tried expanding the list into new rows in Power Query. This lets me filter by individual tags, but then each person appears multiple times—once for each value in the multi-value column—which throws off totals and counts.

What I need:

1. Each person (record) should appear only once in the dashboard.
2. I want to be able to filter on individual tags/items from the multi-value column.
3. Ideally, the multi-value column is shown as a semicolon-delimited string (e.g., “Item A; Item B; Item C”) but still filterable by individual tags.

Has anyone solved this? Is there a way to transform or model the data so I can both keep unique rows and filter on individual items?

Thanks in advance!

[dBlackstock · Accepted solution]

Hi, I received a solution from an external colleague to this Fabric Community. 

[v-achippa]

Hi @dBlackstock,

Thank you for confirming that your issue is resolved now. I request you if possible please share the solution here and mark it as accept it as a solution so that other community members will find it more easily. If not kindly accept the above post as solution. 

Thanks and regards,

Anjan Kumar Chippa

[lbendlin]

Power BI is creating a separate row for each of those values—so I end up with a lot of duplicate rows

How is that an issue?

Is there a way to transform or model the data so I can both keep unique rows and filter on individual items?

The Power BI visuals are taking care of that for you - they automatically aggregate.

[dBlackstock]

That is an issue because it is skewing my total entries. The example that I posted shows that three incidents were submitted, but that is inaccurate. One incident was submitted with three different incident types listed. I don't want duplicate rows in my dashboard table. I want one row per incident, with the incident types all listed in the single row. Does that help to clarify? Thanks!

[lbendlin]

Instead of Count use Count (Distinct) - that will give you the correct number of incidents and gives you the flexibility to report on the incident type independently.

[dBlackstock]

I tried that, and I wish that worked. However, when I change from "Don't Summarize" on the table to Count (Distinct) for that column, it uses a number rather than listing out all of the values in those fields. Here is a screenshot:

How do I list all the values in the one entry line while keeping the totals accurate? 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[v-achippa]

Hi @dBlackstock,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue? if not please provide sample data that covers your issue or question completely.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @dBlackstock,

We wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa