Validate each row in a table with all the rows\Conditions in other table in Power Query

[SaiMadhur]

Hello!

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, If the user from Table 1 meets any of the conditions from Table 2, the request can be approved. Is it possible?

Please refer to the sample tables below

Table 1

Users	Start Date	End Date	Fully paid	Discussion
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

[v-yiruan-msft]

Hi @SaiMadhur ,

You can achieve it in Power Query Editor by adding a custom column just like below screenshot, please copy and paste the following codes in your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjAyBDJNEUylWJ1oJSewtBFCGs70ywcrcAapBEkYwyQiU4vBMi5grSZw4+BaXMESpggzTdG1usEMNUO2LRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Users = _t, #"Start Date" = _t, #"End Date" = _t, #"Fully paid" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Users", type text}, {"Start Date", type date}, {"End Date", type date}, {"Fully paid", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Decision", each if ([Start Date]<>null and [End Date]<>null ) 
or ( ([Start Date]=null or [End Date]=null ) 
      and [Fully paid]="Yes"  ) 
then "Approve" 
else "")
in
    #"Added Custom"

Best Regards

[SaiMadhur]

Hello!

Thanks for the reply, I see the column names "user","Start Date","End date", "Fully paid" are hardcoded in the "Costom Column". The goal is to get each formula from Table 2 and validate it against all the relavent data points for each user dynamically. (Formulas\Rules in table 2 can be defined in a way the power query can recogniz the column names from Table 1 and give the result).

Currently there are 20 formulas in acrual data set that needs to be validated against the user lisy (May be 100000 rows in table 1) in real data set.

If any formula\rule has to be changed\added, it can directly be added to the Table 2 where we don't need to update Query manually to apply the changes.

[v-yiruan-msft]

Hi @SaiMadhur ,

I'm not clear about your below requirement... Do you mean that Table 2 is similar to a rule table, then you need to dynamically change the conditions according to the rules of table2? Could you please provide more details(example, screenshot etc.) about your requirement? Thank you.

The goal is to get each formula from Table 2 and validate it against all the relavent data points for each user dynamically. (Formulas\Rules in table 2 can be defined in a way the power query can recogniz the column names from Table 1 and give the result).

---

Best Regards

[lbendlin]

The LEX & YACC Page (compilertools.net)

[lbendlin]

Please do let me know what you end up with - this is an interesting topic.

[SaiMadhur]

My Bad, I said it is hard with out really knowing about LEX and YACC.  Is LEX and YACC independent technologies\tools? Would you mind telling what is LEX and YACC stands for? (There are many terms refering to LEX and YACC online).

Thanks In Advance!

[lbendlin]

You cannot do that in DAX as the EVALUATE() command is not available in Power BI Desktop .

You can do this in Power Query with Expression.Evaluate()- but technically this would amount to the creation of yet another meta language.  (if you are interested, read up on the history of LEX and YACC)

How many condition are you planning to check for?

[SaiMadhur]

Hello!

Thanks for the quick reply! I see it's getting little complicated, I'll use VBA to solve the problem and will think through LEX and YACC.

We currently have 20 condition to validate and it may increase in future.

Thanks