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

Wresen's avatar
Wresen
Post Patron
1 year ago

Hi Omid_Motamedise 

it gives me someting strange Record Tools lights up 🙂
But not the error as before 

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

Yes, it is not complete and need to add another part including Record.Field, but I am wondering until this step, does it align with the direct query?

  • 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

    I get no errors, The table is not column Left to right , its Top to botton instead

  • 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