Forum Discussion
mapping 2 tables with 2 fields
Hello,
i'm need to filter a table based in one column from a table, but i've to check if that field exists in tw0 columns from the other table.
for example i've a code in table A, and i want to check if that code apears on field 2 ou field 3 from other table.
how can i do that ?
thank you,
JR
hi,@jppuam
After my research, you can do these as below:
Assume that we have to table like these:
table 1:
table2;
Step1:
use LOOKUPVALUE() function to search match value
Look Column 2 = LOOKUPVALUE(Table1[Column1],Table1[Column1],Table2[Column2])
Look Column 3 = LOOKUPVALUE(Table1[Column1],Table1[Column1],Table2[Column3])
Step2:
Use this formula to create a table for this match values
Table = DISTINCT(UNION(FILTER(VALUES(Table2[Look Column 2]),Table2[Look Column 2]<>BLANK()),FILTER(VALUES(Table2[Look Column 3]),Table2[Look Column 3]<>BLANK())))
Result:
If not your case,
please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
5 Replies
You might look at using the LOOKUPVALUE() function as a calculated column. It operates similarly to a VLOOKUP() in Excel. If the data is in a different table, the source and destination tables have to have a relationship.
Hello,
what i'm trying to do is to check if a field 1 is equal to reference or equal to object (these 2 fields in other table).
can i create a new table with the result of the matches ?
thanks,
JR
How can i put in the filter area one field (removing the null values) or another field ?
i want to put :
filter dep1 (all removing the nulls) or dep2 when equals to something ?
thanks
JR
hi,@jppuam
After my research, you can do these as below:
Assume that we have to table like these:
table 1:
table2;
Step1:
use LOOKUPVALUE() function to search match value
Look Column 2 = LOOKUPVALUE(Table1[Column1],Table1[Column1],Table2[Column2])
Look Column 3 = LOOKUPVALUE(Table1[Column1],Table1[Column1],Table2[Column3])
Step2:
Use this formula to create a table for this match values
Table = DISTINCT(UNION(FILTER(VALUES(Table2[Look Column 2]),Table2[Look Column 2]<>BLANK()),FILTER(VALUES(Table2[Look Column 3]),Table2[Look Column 3]<>BLANK())))
Result:
If not your case,
please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
