Select values in one field based on values in other fields

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:

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 Unpivot your three columns in Power Query and then you should be good.