Compare a value from two column and get matching value from another table Power BI

[Mahi1989]

Hi All

As i am fairly new to powerbi need your help in one task on which i am stuck on.

basically i have two tables and i need to compare the value from table one with a row of table 2 and return the output.

Table 1

 I need to compare values in column a & b and get a match from table 2. for example if row 1 has BY Green & BS HIGH. i need to check this value from matrix table below and return the output in column value as either 0 or 1

so if you see Table2 first row has value BY Green and BS low has a value '0'

Regards

Mahi1989

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, right click on the first column in Table2 and select "Unpivot other columns".  This will convert that Table into a 3 column one.  In the Query Editor itself, merge the two tables by connecting the first two columns of Table1 with the first two columns of Table2.

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, right click on the first column in Table2 and select "Unpivot other columns".  This will convert that Table into a 3 column one.  In the Query Editor itself, merge the two tables by connecting the first two columns of Table1 with the first two columns of Table2.

[Mahi1989]

Hi Ashish.. Thanks for suggesting the solution. This really worked. But there has been a constraint added to original problem. basically a new column "Time" in table 1 before value column and we have values in time column which needs to be taken into consideration also. say we have two below rows

Col A	Col B	Time	Value
BY Green	BS High	1	0
BY Green	BS High	5	1
BY Red	BS Low	1	0

the ask is for few combinations not all we have to take time value also in consideration. lets say combination BY Green BSHigh as per table two value this is illegal. but if time value is >=5 for this then the value becomes legal. Please bear in mind that this condition is not for all combinations only for few of them.

Regards Mahi1989

[Ashish_Mathur · Accepted solution]

Hi,

Once you bring over the Value column from Table2 to Table1, write this calculated column formula in Table1

=if [Column A] ="BY Green" and [Column B]="BS High" and [Value]>=5 then 1 else 0

Hope this helps.