Forum Discussion

iWonder's avatar
iWonder
Helper I
5 years ago
Solved

Merge Queries with an IF

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!

 

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

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

6 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    5 years ago

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

    • iWonder's avatar
      iWonder
      Helper I
      5 years ago

      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's avatar
        iWonder
        Helper I
        5 years ago

        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

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      5 years ago

      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