Select values in one field based on values in other fields

[richard_thurau]

Hello,

I have a table of people and qualifications. Each qual is indicated by an "X" in the qualification field. 

I'd like to create a user slicer where I could select all the people with one or more qualifications.

I'm pretty sure this is a data transformation problem, but I can't get my head around what exactly to do.

I'd like to filter "Qual 1" which would return Joan and John. 

Filtering Qual 2 OR Qual 3 would return John and Shari. 

Thanks for your time.

Person	Qual1	Qual2	Qual3
Joan	X
John	X	X
Shari			X

Seems like creating a table like this would work, but I'm not sure how to do it:

Person	Qual
Joan	Qual1
John	Qual1
John	Qual2
Shari	Qual3

[DataNinja777 · Accepted solution]

Hi @richard_thurau ,

You’ve got a table of people and their qualifications, with each qualification represented as a separate column. An "X" means that person holds that qualification. The issue is that this wide table structure isn’t very slicer-friendly in Power BI—you can’t easily say “show me everyone with either Qual2 or Qual3,” because those qualifications are stuck in their own columns.

To fix that, we need to reshape the data into a more analysis-friendly format, where each row is a unique person/qualification combination. This makes it easy to filter by any qualification and automatically return the people who have it. You’re basically turning columns into rows. Here’s how you do that in Power Query.

Open Power Query and use this transformation logic:

let
    Source = YourTableNameHere,
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Person"}, "Qualification", "Value"),
    Filtered = Table.SelectRows(Unpivoted, each [Value] = "X"),
    Cleaned = Table.RemoveColumns(Filtered, {"Value"})
in
    Cleaned

What this does:

• UnpivotOtherColumns collapses all the Qual1, Qual2, and Qual3 columns into two columns: one for the qualification name, and one for the "X" or blank.
• Then we filter out the blanks, keeping only rows where the value is "X".
• Finally, we drop the "Value" column since it’s no longer needed (it was just used for filtering).

The result will be this nice, clean table:

Person	Qualification
Joan	Qual1
John	Qual1
John	Qual2
Shari	Qual3

With this new table, you can create a slicer on the Qualification field, and it’ll filter your visuals to show only the people who have the selected qualification(s). For example, if you select Qual1, it’ll show Joan and John. If you select Qual2 or Qual3, it’ll show John and Shari.

Now you’ve turned a spreadsheet that was a bit awkward to analyze into a sleek, responsive data model.

Best regards,

[richard_thurau]

Nicely demonstrated and explaine. I ended up just using the GUI tools in Power Query to carry out these steps, but this was the explanation my brain needed! 

Thank you.

[Greg_Deckler · Accepted solution]

@richard_thurau Unpivot your three columns in Power Query and then you should be good.

[DataNinja777 · Accepted solution]

Hi @richard_thurau ,

You’ve got a table of people and their qualifications, with each qualification represented as a separate column. An "X" means that person holds that qualification. The issue is that this wide table structure isn’t very slicer-friendly in Power BI—you can’t easily say “show me everyone with either Qual2 or Qual3,” because those qualifications are stuck in their own columns.

To fix that, we need to reshape the data into a more analysis-friendly format, where each row is a unique person/qualification combination. This makes it easy to filter by any qualification and automatically return the people who have it. You’re basically turning columns into rows. Here’s how you do that in Power Query.

Open Power Query and use this transformation logic:

let
    Source = YourTableNameHere,
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Person"}, "Qualification", "Value"),
    Filtered = Table.SelectRows(Unpivoted, each [Value] = "X"),
    Cleaned = Table.RemoveColumns(Filtered, {"Value"})
in
    Cleaned

What this does:

• UnpivotOtherColumns collapses all the Qual1, Qual2, and Qual3 columns into two columns: one for the qualification name, and one for the "X" or blank.
• Then we filter out the blanks, keeping only rows where the value is "X".
• Finally, we drop the "Value" column since it’s no longer needed (it was just used for filtering).

The result will be this nice, clean table:

Person	Qualification
Joan	Qual1
John	Qual1
John	Qual2
Shari	Qual3

With this new table, you can create a slicer on the Qualification field, and it’ll filter your visuals to show only the people who have the selected qualification(s). For example, if you select Qual1, it’ll show Joan and John. If you select Qual2 or Qual3, it’ll show John and Shari.

Now you’ve turned a spreadsheet that was a bit awkward to analyze into a sleek, responsive data model.

Best regards,