Forum Discussion
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 🙂 ...
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
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.