Forum Discussion
Helper IIHow to do VLOOKUP functions in Power BI?
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!
10 Replies
- parry2k
Super User
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?
kwpbiThank 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.
- 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:
