Forum Discussion
If statements between 2 tables , Power Query
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
I see that this is not working when i have a Direct Query .Get an error that says that its not supported in DirectQ.
Is it possible to do this in Direct Q ??
The Fact table is important and the Table1 is a Direct Q
/Thanks
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"
Hi Omid_Motamedise
it gives me someting strange Record Tools lights up 🙂
But not the error as beforeYes, 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?
I get no errors, The table is not column Left to right , its Top to botton instead