How to do VLOOKUP functions in Power BI?

[kwpbi]

I am reposting this question in a more clear and concise manner with the hope that someone can/will help.

I have two tables. Table 1 has work order # and "scrap qty". Table 2 has work order # and "good qty". 

In excel, I would do the following:

1) Sum up all "good qty's" for any recurring work order #'s in table 2 (add together any duplicates).

2) Create new column in table 1 that uses a 'vlookup' formula to find the correct "good qty" in table 2 based on work order #.

It's very simple in Excel. But in Power BI? Not as straight-forward. At least not for me. Your help is greatly appreciated!!

Thank you!

[jdbuchanan71]

Hello @kwpbi 

You can get to what you are looking for, it is just a bit of a shift from Excel to PowerBI.  

First we create a table that has all the work order numbers.  In my example I have the tables called 'good' and 'scrap'.  My combined table is called 'work orders'

work orders = 
DISTINCT (
    UNION ( 
        DISTINCT(good[work order #]),
        DISTINCT(scrap[work order #])
    )
)

Then we join that to each of the detail tables:

Next we write a couple measures to sum the 'good qty' and 'scrap qty'

Good Amount = SUM (good[good qty] )

Scrap Amount = SUM ( scrap[scrap qty] )

And now you pull the work order # from your 'work orders' table and your two measures into a visual:

[parry2k]

@kwpbi your question is still not very clear, it is always good idea to show some data sample. based on your input i think this is how your dataset look like

Table1 -> This doesn't have duplicate work order number, correct?

WO   Scrap Qty

1         100

2         200

3         300

Table2 -> This has duplicate work order number, correct?

WO   Good Qty

1         100

1          200

1         300

2         200

3         300

3         400

End result you are looking for

WO    SCrap Qty    Good Qty (sum of good qty from table 2 for each WO)

1            100                 600

2             200                200

3             300                700

Is above correct understanding what you are looking for?

[kwpbi]

Thank you for the prompt response.

The only correction is that there are duplicate work order #'s in BOTH tables. But in table 1, I do not want to combine them. Here's some more info:

Table 1 data only contains work orders that had >0 parts scrapped. There can be several entries for one work order # because parts may have been scrapped at more than one work center before the order was completed. I do not want to sum these rows together because I will lose that work center data.

Table 2 contains EVERY work order. There should only be one "good qty" for each work order, but mistakes get made and corrected on occasion, resulting in duplicate transactions (these need to be combined).

So in summary, step 1 is to combine those good quantities so there are no duplicates in table #2. Step 2 is to simply add those good values to a new column in table #1 by matching up the work order #, without combining the duplicates in table 1.Here is an example:

Table #1 (original)

Work Order #          Scrap Qty

5551                        3

5551                        1

5552                        1

5554                        4

5554                        1

Table #2 (original)

Work Order #          Good Qty

5550                        12

5551                        36

5551                        -8

5552                        15

5553                        21

5554                        60

New Table #2 (duplicates combined)

Work Order #          Good Qty

5550                        12

5551                        28

5552                        15

5553                        21

5554                        60

New table #1 (with good qty column added)

Work Order #          Scrap Qty          Good Qty

5551                        3                        28

5551                        1                        28

5552                        1                        15

5554                        4                        60

5554                        1                        60

I hope that isn't too much info!

Thanks again for your help.

[jdbuchanan71]

If you pull in the work order # from the combined table and the scrap # from the scrap column of the scrap table and set that field to 'do not summarze' and the [Good Amount] measure you should get what you are looking for.  It will repeate the full good amount on each work order line but the srap amount will be ech entry from the scrap table.

[kwpbi]

@jdbuchanan71 wrote:

If you pull in the work order # from the combined table and the scrap # from the scrap column of the scrap table and set that field to 'do not summarze' and the [Good Amount] measure you should get what you are looking for.  It will repeate the full good amount on each work order line but the srap amount will be ech entry from the scrap table.

I'm sorry but I don't really understand what you are describing, or how to do it. If I had more experience with Power BI it might make more sense but at this point I wouldn't know where to start (how to make that "combined table" for example).

---

---

[parry2k]

@kwpbi here are the steps;

- add table visual

- put work order number from table 1 on values

- put scrap qty from table 1 on values, and there is arrow key next to scrap qty on values, click that, and choose don't summarize

- put good qty from able 2 on values

you will get the result.

if it doesn't work then tell step by step what you did and what you are getting

[kwpbi]

---

@parry2k wrote:

@kwpbi here are the steps;

- add table visual

Do you mean create "New Table" on the "Modeling" tab? I'm working with Power BI Desktop, by the way. not sure if that matters but I forgot to mention it.

When I try to create a new table, it wants information in the formula bar. I'm not sure what to put there. If I don't put anything in there, it doesn't make a table. If I make it equal another table, the two become linked and I cannot change the new table without it also changing the original table.

- put work order number from table 1 on values

I don't know what "on values" means. Can you explain what ribbon tab(s) and command(s) I would be using to do that? Or explain in more detail what you mean by putting something "on values"?

- put scrap qty from table 1 on values, and there is arrow key next to scrap qty on values, click that, and choose don't summarize

- put good qty from able 2 on values

you will get the result.

if it doesn't work then tell step by step what you did and what you are getting

Like I said, I don't even know where to start. All I want to do is create a new table that only contains the work order number column and good qty column from my original table, and then sum up the good qty's for any recurring work order numbers in that table.

Here is what I tried to do:

step 1: Create new table from original table, enter this in formula bar (Table = AX_TRANSACTIONS)

step 2: No matter what I do next, it affects both tables. I need my original table to stay as-is. So I am stuck here. I do not know how I can complete the simple task of making a new table that has only those two columns, with summed up good qty's, without affecting my original table. Frustration station!

---

[parry2k]

@kwpbi not new table on modelling tab but TABLE VISUAL.

Don't mind but you need to take some basic training on PowerBI to understand some of the terms. There is guided learning and I will highly recommend to take that when you get chance. It will help.

[parry2k]

@kwpbi you need to set many to many relationship between table 1 and table 2 on Work order. As far as you don't care about work order which doesn't exists in table 1 but table 2 (for example 5550),  solution will work.

[kwpbi]

---

@parry2k wrote:

@kwpbiyou need to set many to many relationship (for example 5550),  solution will work.

---

It already is a many-to-many relationship (can't be anything else).

I'm not sure where to start in Power BI to accomplish this task. The logical first step, as I have mentioned, is to create a new table that totals up all duplicate work order rows in my "good qty" table. Then I believe I should be able to create a one-to-many relationship between that new table (with the duplicates combined) and my "scrap qty" table to get the new column I am after. Does that sound right?

If so, then maybe all I need help with is how to create that new table where the duplicate work orders have been combined?

thanks.