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 🙂

i have two tables , Table1 and Table2 

 


i would like to create a new column in Table1 where i check the statements.
If Table1[ID] = Table2[ID] && Table1[Value] >= Tabel2[Check1] && Table1[Value] <= Table2[Check2] then Table2[Return]

also if error or not match at all it gives "Not found"

Someting like this:




Thanks so much for any help to point me in the right direction

 

 

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

11 Replies

  • Claude_Xu's avatar
    Claude_Xu
    Frequent Visitor
    1 year ago

    Hi Wresen,

     

    Please refer to below implementation

     

    let
    Table1 = #table({"ID", "Value"}, {
        {1,10},
        {2,11},
        {3,12},
        {4,13},
        {5,22},
        {2,8}
    }),
    Table2 = #table({"id", "Check1", "Check2", "Return"},{
        {1,5,10,"Green"},
        {2,6,10,"Blue"},
        {3,11,22,"Brown"},
        {4,1,14,"Yellow"},
        {2,7,12,"Red"}
    }),
    #"Left Join Table" = Table.Join(Table1, "ID", Table2, "id", JoinKind.LeftOuter),
    #"Add Custom Column" = Table.AddColumn(#"Left Join Table", "Custom",
        each try if [Value] >= [Check1] and [Value] <= [Check2] then [Return]
                else "Not Found"
            otherwise "Not Found"
    ),
    Result = Table.Group(#"Add Custom Column", {"ID", "Value"},{
        {"Custom", each if Table.RowCount(_) = 1 then [Custom]{0}
                        else Text.Combine(List.RemoveItems([Custom], {"Not Found"}), ", ")}
    })
in
    Result

     

     

    It seems you did not mention the logic of some edge cases. For example, if you have [ID=2, Value=8] in Table1, there will be two matching records in Table2. The above implementation simply joins two matching 'Return' string literals with a comma.

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    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