Forum Discussion
Matching Columns in 2 Tables
I have two tables as shown below. I want to validate and match the values from Product and category column of table 1 with Product and category column of Table 2 else it should return “Incorrect Value”.
Table 1 is being updated by the staff manually in the excel whereas Table 2 is the database from where I want the information to be validated.
Instead of creating a lookup table, can this be done through measure? Pls suggest the best option.
Table 1
| Ref ID | Product | Category |
| 2349898729 | WW | Y |
| 2449898728 | WW | Y |
| 2549898727 | WW | O |
| 2649898726 | WW | O |
| 2749898725 | KK | Y |
| 2849898724 | KK | Y |
| 2949898723 | KK | Y |
| 3049898722 | KK | Y |
| 3149898721 | KK | W |
| 3249898720 | WW | E |
| 3349898719 | WW | E |
| 3449898718 | TT | R |
| 3549898717 | TT | R |
| 3649898716 | TT | R |
| 3749898715 | TT | R |
| 3849898714 | PP | M |
| 3949898713 | PP | M |
| 4049898712 | PP | N |
Table 2
| Product | Category |
| WW | Y |
| WW | O |
| KK | Y |
| TT | R |
| PP | M |
| PP | N |
How the result should be displayed
| Ref ID | Product | Category | Result |
| 2349898729 | WW | Y | Valid |
| 2449898728 | WW | Y | Valid |
| 2549898727 | WW | O | Valid |
| 2649898726 | WW | O | Valid |
| 2749898725 | KK | Y | Valid |
| 2849898724 | KK | Y | Valid |
| 2949898723 | KK | Y | Valid |
| 3049898722 | KK | Y | Valid |
| 3149898721 | KK | W | Incorrect Value |
| 3249898720 | WW | E | Incorrect Value |
| 3349898719 | WW | E | Incorrect Value |
| 3449898718 | TT | R | Valid |
| 3549898717 | TT | R | Valid |
| 3649898716 | TT | R | Valid |
| 3749898715 | TT | R | Valid |
| 3849898714 | PP | M | Valid |
| 3949898713 | PP | M | Valid |
| 4049898712 | PP | N | Valid |
gauravnarchal , Try the option
new Measure = if(isblank(countx(filter(Table1, Table1[product] = max(Table2[product]) && Table1[Category] = max(Table2[Category])),Table2[Category])) ,"Incorrect Value", "Valid")
new column = if(isblank(countx(filter(Table2, Table2[product] =Table1[product] && Table2[Category] =Table1[Category]),Table2[Category])) ,"Incorrect Value", "Valid")
hi gauravnarchal,
I have created the required solution using check at the table level - see below for the steps to create the same
Note: You can hide the Key column from report view if you do not want users to see the same.
1. Create key column for Mapping table
Key = 'Mapping table'[Product] & "-" & 'Mapping table'[Category]2. Create key column in Data tableKey = 'Data Entry Table'[Product] & "-" & 'Data Entry Table'[Category]3. Create relationship between the "Key" columns between both tables4. Create check column in Data tableCheck = if ('Data Entry Table'[Key] = RELATED('Mapping table'[Key]) , "Valid", "Incorrect Value")5. Create a table visual with the required dataPlease mark the post as a solution if my comment helped with solving your issue. Thanks!
3 Replies
gauravnarchal , Try the option
new Measure = if(isblank(countx(filter(Table1, Table1[product] = max(Table2[product]) && Table1[Category] = max(Table2[Category])),Table2[Category])) ,"Incorrect Value", "Valid")
new column = if(isblank(countx(filter(Table2, Table2[product] =Table1[product] && Table2[Category] =Table1[Category]),Table2[Category])) ,"Incorrect Value", "Valid")
Thanks amitchandak .
Also, can you please let me know how can i compare only the product columns from table 1 & table 2?
hi gauravnarchal,
I have created the required solution using check at the table level - see below for the steps to create the same
Note: You can hide the Key column from report view if you do not want users to see the same.
1. Create key column for Mapping table
Key = 'Mapping table'[Product] & "-" & 'Mapping table'[Category]2. Create key column in Data tableKey = 'Data Entry Table'[Product] & "-" & 'Data Entry Table'[Category]3. Create relationship between the "Key" columns between both tables4. Create check column in Data tableCheck = if ('Data Entry Table'[Key] = RELATED('Mapping table'[Key]) , "Valid", "Incorrect Value")5. Create a table visual with the required dataPlease mark the post as a solution if my comment helped with solving your issue. Thanks!
