Forum Discussion
Lookup function returns Error
Hi all,
I have a problem to make a Lookup function work in my report. I get the error "A table of multiple values was supplied where a single value was expected". I have tried to use the classic LOOKUPVALUE, but it returns the error mentioned. As far as I can understand it's do to the many dublicating values?
My tables are looking like this:
Products table:
| Material | Class |
| 156135 | A1 |
| 156834 | D4 |
| 156749 | A1 |
| 174625 | B2 |
| 982496 | C3 |
| 447813 | A1 |
| 176317 | B2 |
| 489137 | D4 |
| 478137 | E5 |
The table from which I want to look up in looks like this:
| Class | Qty |
| A1 | 3 |
| B2 | 3 |
| C3 | 9 |
| D4 | 6 |
| E5 | 6 |
I expect my Products table to look like this after the Lookup
| Material | Class | Qty |
| 156135 | A1 | 3 |
| 156834 | D4 | 6 |
| 156749 | A1 | 3 |
| 174625 | B2 | 3 |
| 982496 | C3 | 9 |
| 447813 | A1 | 3 |
| 176317 | B2 | 3 |
| 489137 | D4 | 6 |
| 478137 | E5 | 6 |
So all the Materials are unique, but the Classes and it related QTY can be similar.
Any idea which formula I could use to make it work, and what relationship should the two tables have?
Best regards!
nbrandborg , New column in Table 1
sumx(filter(Table2, Table1[Class] = Table2[Class]), Table2[qty])
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
4 Replies
nbrandborg , New column in Table 1
sumx(filter(Table2, Table1[Class] = Table2[Class]), Table2[qty])
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8Exactly the solution I was looking for. Perfect! Thank you amitchandak
Hi nbrandborg
This should work as a calcualted column in your products table
Qty = LOOKUPVALUE(LookUpT[Qty], LookUpT[Class], ProductsT[Class])You can also do all this in PQ though a simple merge. Probably best
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi nbrandborg .
The best possible solution would be to do the same using Merge in Power Query.
Regards,
Sanket Bhagwat
