I need to create a calculated column based on some rules which are in a another table

[Anonymous]

The rules are as below (example) -

1. The original table has columns Country, Product and Revenue. 

2. A new column is to be created based on the rules mentioned below. These list of rules is pretty huge (Approx 100+) Hence a manual switch statement or a if else  loop would not be a suitable approach. 

3. I can map the rules for which there is only 1unique value such as Row 1, however how to deal with condition such as row 2, where it is applicable for all the countries.

I was thinking of using a lookupvalue function to map the outcome, but for instances such as row 2, it becomes difficult. 

		Inputs
Row NUmber	Target Column	Country	Product	Revenue
1	A1	US	A	<50
2	C1	<ALL>	C	>100
3	D1	Germany	D	<Any>
4	X1	US	<ALL>	>50 and <100

[amitchandak]

@Anonymous , You can use lookup, You can call the other table in filter

refer these

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Anonymous]

Hi Amit, 
Thanks for your response. Yes I am currently doing it the same way.

However for the second row where the country maps to any country, in this scenario how can we look up. 

Source	Lookup Table
US	US
Germany	Germany
will not be matched for countries	<Any>

The thir instance in above scenario will not match for any countries from source. One solution I can think of is to replicate the combination for all the listed countries.  What could be another easier one. 

[amitchandak]

@Anonymous , I such scenario, Create a table like this and join country with the country all to get all rows

Merge in power query

Country	Country All
US	US
Germany	Germany
US	All
Germany	All