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

Check this code, (it results in recodr, but just let me know does it work in Direct Query)

 

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.First(Table.AddColumn(T1, "Custom", each Table.SelectRows(T2, (x)=> x[ID]=_[ID] and x[Check 1]<=_[Value] and x[Check 2]>=_[Value])) )
    in
    #"Added Custom"

 

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?

    • 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

      • 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"