Forum Discussion
Wresen
1 year agoPost Patron
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 🙂 ...
Omid_Motamedise
1 year agoSuper User
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
1 year agoPost Patron
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