Compare each row in a table with all the rows in other table in Power Query

Question

I have a scenario to validate each row in Table 1 with all the conditions in Table 2 to make a decision whether to approve or reject a request in power Query, Is it possible?

Please refer to the sample tables below

Table 1

  Users   Start Date    End Date    Fully paid   Decision
     A     1/1/2021     5/1/2021  
     B     1/2/2021     5/2/2021          No
     C                  5/3/2021          Yes
     D     1/4/2021                       No
     E     1/5/2021     5/5/2021          Yes
     F                  5/6/2021          No

Table 2

Conditions                                                           Decision
If start date and end date are not null                               Approve
If start date and end date are null but fully paid is Yes             Approve

Answer 1

Thanks for the Quick response!, This is a static way of applying conditions. If the condition changes, we need to change the condition manually in the query. I was looking for a little more dynamic solution, something like directly referring to the conditions from the second table.

If some conditions needs to be changed, the conditions are applied automatically and decisions are made when the query is refreshed.

Please let me know your thoughts.

Answer 2

This can be done - I have done that in the past - my use case was to create dynamic definitions of new columns in Excel with complex conditions.

It comes down to translating your rows to if / else statenements dynamically using M. One you've done that (and it takes some time do do that - you need to employ recursion to process entire list and output a single string with all conditions), all you have to do is Expression.Evaluate your string.

So in short, you need to have entire Table.AddColumn command created as string dynamically.