Filter out rows where condition is met in another row

[Anonymous]

Hi,

I am trying to filter out rows where the value in one column is equal and values across any row meet a certain condition. We have a security table which is comprised of three groups. For the sake of sensitive data, I have created a sample dataset where these three categories are color, number, and animal. 

When a user has access to ALL members of each group, our security table will provide a row for each possible combination and a row formatted as: "#-#-#" meaning, the user has access to all data. Where a user has access to all data, I want to filter out the combination rows as it's repetitive. Is there a way to achieve this in Power Query?

The link below is the sample data set. You can see each of the categories and their list members, all possible combinations, and the access assignments for three users, Anna, Gail, and Brandon. 

There are two potential outcomes I'm looking for: (1) delete the rows of unneccessary data or (2) create a column that can be used as a filter to hide the unneccessary rows of data. 

Google Sheet - User Security Sample 

Thank you!

[DataInsights · Accepted solution]

@Anonymous,

Try this Power Query solution.

1. Create query FullAccess by filtering for "#-#-#".

2. Create query PartialAccess using a Left Anti join. The concept is to return users that are not in FullAccess. Remove the resulting column FullAccess (unneeded).

3. Create query FinalSecurityTable which is a union of FullAccess and PartialAccess.

[Anonymous]

I was able to do the first step, as shown below. I have two criteria that I want to be kept in this table, so I did a variation of what you provided. Before I did this, I included only one criteria ("#-#-#-ebit"). When I moved on to the second step, the only rows that were brought into the table were the rows with a security_key value of "#-#-#-noebit". When I came back to step one and added this value to the criteria, the second table is now completely blank. I don't see any issues with the written query itself, but I could be missing it. 

Any idea of what may be happening here?

[DataInsights]

@Anonymous,

Adding an additional filter to step 1 shouldn't cause an issue. Is "COPA ALL" the query in your first screenshot? You might try joining on employee_userid instead of employee_email (guessing here, since I'm not familiar with your data). Here's my M code for step 2:

let
    Source = OriginalSecurityTable,
    MergeFullAccess = Table.NestedJoin(Source, {"User"}, FullAccess, {"User"}, "FullAccess", JoinKind.LeftAnti),
    RemovedColumns = Table.RemoveColumns(MergeFullAccess,{"FullAccess"})
in
    RemovedColumns

If this doesn't work, you can share a sanitized pbix via one of the file services like OneDrive and I'll be happy to take a look.

[Anonymous]

I think I got it resolved - your M code was helpful. It looked like I had an extra filter on my original source table, and once I removed that, it populated the final table like I would expect. Thank you so much for the help!!