Forum Discussion

Wresen's avatar
Wresen
Post Patron
1 year ago
Solved

If statements between 2 tables , Power Query

Hi and thanks for reading this. i have looked around and i cant seem to find any answer for my problem not sure how easy or hard this is , have only been able to get true /false check to work 🙂 ...
Need Help
  • Omid_Motamedise's avatar
    Omid_Motamedise
    1 year ago

    Use this formula


    let
    T1=Table.FromColumns({{1..5},{10,11,12,13,22}},{"ID","Value"}),
    T2=Table.FromColumns({{1,2,3,4,2},{5,6,11,1,7},{10,10,22,14,12},{"Green","BLue","BRown","Yellow","Red"}},{"ID","Check 1","Check 2","Return"}),
    #"Added Custom" = Table.AddColumn(T1, "Custom", each try Table.SelectRows(T2, (x)=> x[ID]=_[ID] and x[Check 1]<=_[Value] and x[Check 2]>=_[Value])[Return]{0} otherwise "Not Found")
    in
    #"Added Custom"

     

    For more descrition see this video:
    https://www.youtube.com/watch?v=DHB2ejpEGxU

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

So, now check the below codes and let me know which one does work on direct query.
S1:

let
    T1=Table.FromColumns({{1..5},{10,11,12,13,22}},{"ID","Value"}),
    T2=Table.FromColumns({{1,2,3,4,2},{5,6,11,1,7},{10,10,22,14,12},{"Green","BLue","BRown","Yellow","Red"}},{"ID","Check 1","Check 2","Return"}),
    #"Added Custom" = Table.AddColumn(T1, "Custom", each Table.First(Table.SelectRows(T2, (x)=> x[ID]=_[ID] and x[Check 1]<=_[Value] and x[Check 2]>=_[Value]))[Return]) 
    in
    #"Added Custom"



 

S2:

let
    T1=Table.FromColumns({{1..5},{10,11,12,13,22}},{"ID","Value"}),
    T2=Table.FromColumns({{1,2,3,4,2},{5,6,11,1,7},{10,10,22,14,12},{"Green","BLue","BRown","Yellow","Red"}},{"ID","Check 1","Check 2","Return"}),
    #"Added Custom" = Table.AddColumn(T1, "Custom", each Record.Field(Table.First(Table.SelectRows(T2, (x)=> x[ID]=_[ID] and x[Check 1]<=_[Value] and x[Check 2]>=_[Value])),"Return")) 
    in
    #"Added Custom"

S3:

let
    T1=Table.FromColumns({{1..5},{10,11,12,13,22}},{"ID","Value"}),
    T2=Table.FromColumns({{1,2,3,4,2},{5,6,11,1,7},{10,10,22,14,12},{"Green","BLue","BRown","Yellow","Red"}},{"ID","Check 1","Check 2","Return"}),
    #"Added Custom" = Table.AddColumn(T1, "Custom", each List.First(Table.Column(Table.SelectRows(T2, (x)=> x[ID]=_[ID] and x[Check 1]<=_[Value] and x[Check 2]>=_[Value]),"Return"))) 
    in
    #"Added Custom"

S4:

let
    T1=Table.FromColumns({{1..5},{10,11,12,13,22}},{"ID","Value"}),
    T2=Table.FromColumns({{1,2,3,4,2},{5,6,11,1,7},{10,10,22,14,12},{"Green","BLue","BRown","Yellow","Red"}},{"ID","Check 1","Check 2","Return"}),
    #"Added Custom" = Table.AddColumn(T1, "Custom", each Table.Column(Table.SelectRows(T2, (x)=> x[ID]=_[ID] and x[Check 1]<=_[Value] and x[Check 2]>=_[Value]),"Return"){0}) 
    in
    #"Added Custom"

 

Wresen's avatar
Wresen
Post Patron
1 year ago

Hi Omid_Motamedise 
First thx so much for helping me out there.
this is what i got :

S1 get Error in the custom and Not supported in DirectQ

S2 get Error in the custom and Not supported in DirectQ

S3 Get Null in Custom and Not supported in DirectQ

S4 Get Null in Custom and Not supported in DirectQ


/Thanks