Forum Discussion
Helper IHELP IN LOOKUPVALUE
Hi everyone
I need your help how to wite guery with lookupvalue or other fuction to update cloumn from anther table
Table 1 allitems
| itemid | name |
| 01 | T-shirt |
| 02 | Short |
Table 2 inventtrans
| itemid | qty | cost | category |
| 01 | 1 | 40 | 2345678 |
| 01 | 22 | 40 | 2345678 |
| 02 | 13 | 30 | 4536235 |
the Reltaion between the 2 tables is (1 - *)
allitems.itemid = inventtrans.itemid
I writed this query in allitems table by adding new column category and update it from inventtrans table but did not work
Hi wadda7 - you can use LOOKUPVALUE(), but I suggest the below is faster.
Category = MAXX( RELATEDTABLE('Inventory Transactions'), 'Inventory Transactions'[category] )MAXX (or MAX) simply convert a table of the same value to one scalar value, so think of it like FIRST.
However, LOOKUPVALUE is this:Category 2 = LOOKUPVALUE( 'Inventory Transactions'[category], 'Inventory Transactions'[itemid], 'All Items'[itemid] )My PBIX is here if you want to review.
8 Replies
- edhans
Community Champion
Hi wadda7 - you can use LOOKUPVALUE(), but I suggest the below is faster.
Category = MAXX( RELATEDTABLE('Inventory Transactions'), 'Inventory Transactions'[category] )MAXX (or MAX) simply convert a table of the same value to one scalar value, so think of it like FIRST.
However, LOOKUPVALUE is this:Category 2 = LOOKUPVALUE( 'Inventory Transactions'[category], 'Inventory Transactions'[itemid], 'All Items'[itemid] )My PBIX is here if you want to review.
- wadda7
this one work with me
Category = MAXX( RELATEDTABLE('Inventory Transactions'), 'Inventory Transactions'[category] )thank you
- jennratten
Super User
Hello - this worked just fine for me. I have attached the pbix. If this didn't work for you, check the values in the itemid columns of both tables to ensure they really are the same. It looks like your numbers are formatted as text, so the values may need to be trimmed/cleaned to remove trailing spaces or other whitespace characters. That can easily be done in Power Query by selecting the column(s) > right-click > transform > clean/trim.
CATEGORY = lookupvalue ( inventtrans[category], inventtrans[itemid], allitems[itemid] )- wadda7
Hello jennratten
Yes the itemid column is text type because ite has numbers and text like this
itemid
0965-22345ss
I did the trim and clean and I got this error
in allitems table the itemid is uniqe but in the inventtrans table the itemid can comes more than one time
- jennratten
Did you trim and clean the columns in both tables?
Syndicate_AdminAdministrator
Hello - this worked just fine for me. I have attached the pbix. If this didn't work for you, check the values in the itemid columns of both tables to ensure they really are the same. It looks like your numbers are formatted as text, so the values may need to be trimmed/cleaned to remove trailing spaces or other whitespace characters. That can easily be done in Power Query by selecting the column(s) > right-click > transform > clean/trim.
CATEGORY = lookupvalue ( inventtrans[category], inventtrans[itemid], allitems[itemid] )
