Match between columns of different tables

[nicolasvc]

I have 2 tables, in one I have a value per product and the corresponding month with the year and store, and another table with the detailed cost for the month of each year, per product and store, but to this table I need to add the Value row, but with the value of that row when it matches the Store and Product, month and year (the first table only has one month ,in this case it is the first month, but it could be any, and I need that one) and 0 in case there is no match.

Store	Product	Year	Month	Value
1	A	2021	1	500
1	B	2021	1	1000
...	...	...		...

Store	Product	Year	Month	Cost	Value
1	A	2021	1	1000	500
1	A	2021	2	500	0
1	B	2021	1	5000	1000
1	B	2021	2	50	0

any comment with any help function or any comment, it is appreciated

[TheoC]

Hi @nicolasvc,

You can use Calculated Columns to add a unique code to each table:

col_UK = 'table'[STORE] &" " & 'table'[PRODUCT] &" "& 'table'[YEAR] &" " & 'table'[MONTH] )

and then perform a LOOKUP using a Calculated Column.

col_Lookup = 

VAR _LookUKVal = LOOKUPVALUE( tbl2Facts[Value] , tbl2Facts[col_UK] , tbl1Store[col_UK] )

RETURN 

IF ( _LookUKVal = 0 , 0 , _LookUKVal )

Your output will look like this (just adjust table names).

Hope this helps.

[YukiK]

In Power Query or your ETL tool, you can join table A on to table B on the key that is consisted of Store, Produst, Year, and Month (left join). Replace nulls with 0.

[TheoC · Accepted solution]

Hi @nicolasvc,

You can use Calculated Columns to add a unique code to each table:

col_UK = 'table'[STORE] &" " & 'table'[PRODUCT] &" "& 'table'[YEAR] &" " & 'table'[MONTH] )

and then perform a LOOKUP using a Calculated Column

col_Lookup = 

VAR _LookUKVal = LOOKUPVALUE( tbl2Facts[Value] , tbl2Facts[col_UK] , tbl1Store[col_UK] )

RETURN 

IF ( _LookUKVal = 0 , 0 , _LookUKVal )

Your output will look like this (just adjust table names).  I just adjusted the below column to ensure it returns 0 if there is no value 🙂

Hope this helps.

[nicolasvc]

Thank you very much, what a great idea!