cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SaiMadhur
Regular Visitor

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

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

UsersStart DateEnd DateFully paidDiscussion
A1/1/20215/1/2021  
B1/2/20215/2/2021No 
C 5/3/2021Yes 
D1/4/2021 No 
E1/5/20215/5/2021Yes 
F 5/6/2021No 

 

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
 

 

8 REPLIES 8
yingyinr
Community Support
Community Support

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"

 

custom column.JPG

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbendlin
Super User III
Super User III
lbendlin
Super User III
Super User III

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

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
Super User III
Super User III

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?

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors