Forum Discussion

jppuam's avatar
jppuam
Helper V
7 years ago
Solved

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 che...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

     

     

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

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