Merge Queries with an IF

[iWonder]

Thank you for taking the time to read my question.

I am brand new to Power Query... let's start there...

I have Merged 2 queries in Power Query and have added a Parameter.

One query is a list of users and what customers they have access to, the second query is a list of all the month end data for each customer for each month. The queries are joined on customer name. This way if I filter the user table, the summary table is filtered.

Person A can only see Customer A

Person B can only see Customer B

Person C can see ALL records

If I merge the queries, it works if I enter person A's email address into the parameter because they have actual customer names, but if I put in Person C's email address nothing is returned because they have "ALL" instead of a Customer Name

User           Company

Person A    Customer A

Person B    Customer B

Person C    ALL

= Table.NestedJoin(FlockSummary, {"Title"}, Users, {"CustomerAccess"}, "Users", JoinKind.Inner)

How do I say to the merge, if the User Company = "ALL" then show everything, else show where they are equal?

Once I have this, I'll figure out how to pass an email address to the parameter from Excel

Thanks!

[wdx223_Daniel · Accepted solution]

Table.SelectRows(FlockSummary,each if Users[CustomerAccess]{0}="ALL" then true else [Title]= Users[CustomerAccess]{0})

[Syndicate_Admin]

Hi @wdx223_Daniel 

Thank you so much for your reply and for the formula.

I'm not sure where to put that... do I put it in place of the merged query initial step? Do I add it as a new step to my FlockSummary query (if so, how do you add a new step?)?

Thanks again for your help

[iWonder]

Hi @wdx223_Daniel 

Thank you so much for your reply and for the formula.

I'm not sure where to put that... do I put it in place of the merged query initial step? Do I add it as a new step to my FlockSummary query (if so, how do you add a new step?)?

Thanks again for your help

[iWonder]

Hi @wdx223_Daniel 

After some more thinking, I've selected my FlockSummary query and clicked the fx button. When I do that I get

= #"some big long guid"

I replaced that with your formula.

Then I get "Expression.Error: A cyclic reference was encountered during evaluation."

I must be doing something wrong

[iWonder]

Hi @wdx223_Daniel 

I'm continuing to play around with this and I sorted a column to see the code generated.

= Table.SelectRows(#"BIG LONG GUID", each ([Title] = "users email address here...") and ([CustomerAccess] = "ALL"))

 I filtered the Users query by Title (which is email address [took it out for privacy]) and the CustomerAccess column.

The spot where the table name goes is a BIG LONG GUID. If I change that to Users and click the check mark, I get an error:

"Expression.Error: A cyclic reference was encountered during evaluation"

Do I need to do a rename somewhere?

Thanks

[iWonder]

OK I got it!

I did a Merge Queries as New from the Home tab. It generated the code. I then replaced the generated code with the formula you sent and I'm in business!

Thanks