Power Query - Filtering Groups on Value

Question

I have a source table with data like:

ID	Status
1	TRUE
2	FALSE
3	TRUE
3	FALSE

I have then done a Group By on ID keeping All Rows.

My goal is to then filter the groups to only those where both TRUE and FALSE Status rows exist.

Is this possible?

I don't have much experience with power query at all so I'm a little lost on how to approach.

First attempt was to use something like Table.SelectRows(#'Grouped', each [Status] = 'FALSE'... but this format doesn't work.

---------- Update

Current approach is trying something like:

= Table.SelectRows(#'Grouped Rows', each SomeKindOfAndIDontKnowYet(Table.MatchesAnyRows([allRows], each([Status]="TRUE")), Table.MatchesAnyRows([allRows], each([Status]="FALSE")))

So that it's like select from the grouped table, each row that contains a table that contains any row of both TRUE and FALSE...

Answer 1

Rows.

My goal is to then filter the groups to only those where both TRUE and FALSE Status rows exist.

Can you show your expected result clearly according your source table?

Answer 2

Hello Jake,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

That's correct! Your formula correctly filters groups in a table to include only those where there are both 'true' and 'false' statuses. It uses Table.SelectRows to pick the right groups and Table.MatchesAnyRows to check for both 'true' and 'false' status within each group. This ensures that only groups with both conditions are selected.

Best Regards, Ibhadighi

Answer 3

Got there in the end, my approach in the update above is in the right direction, but the Status values are strings, but booleans, so the condition becomes =true, rather than ="TRUE".

Combine that with the "and" keyword in the selectrows function to check for both.

= Table.SelectRows(#"Grouped Rows", each (Table.MatchesAnyRows([allRows], each [Status]=true) and (Table.MatchesAnyRows([allRows], each [Status]=false))))