CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Mahi
Mahi

Reputation: 37

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

I am fairly new to powerbi and I 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

enter image description here

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 then I need to check this value from matrix table below and return the output in column value as either 0 or 1.

Table 2

enter image description here

As you can see the Table 2 first row has value BY Green and BS low has a value '0'

Upvotes: 0

Views: 6025

Answers (1)

Jbowman
Jbowman

Reputation: 480

Try this...

enter image description here

Index() returns a value from the matrix (in purple) based upon the intersections of the two match()'s. The first is the Vertical match in from the Table1:Col A; the second is the Horizontal match from table1:Col B. The value found at that intersection is returned.

... My apologies ... just saw this was a BI request... no worries...

First, Need fixup table2 as a lookup file:

First, click a cell in table 2 (don't edit), then Data menu >frm table/range, will bring up the Power Query window. Select columns B (not A) through Col F), then in the PQ Transform menu > Unpivot to create the new lookup table. this can either be saved as a new table or be used by reference.

enter image description here

Next, open and merge Table 1 PQ_Table 2 (Be sure to select BOTH Columns in BOTH Tables, in the same order). Then, expand the table tab following the merge expand the table tab. I only selected the value to return but you can return all the values to verify, then delete the unneeded columns.
enter image description here

Hope this helps...

Good Luck.

Upvotes: 1

Related Questions