Forum Discussion
Compare a value from two column and get matching value from another table Power BI
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.
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.
Super UserHi,
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.
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
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.