HELP IN LOOKUPVALUE

[wadda7]

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 

CATEGORY = LOOKUPVALUE(inventtrans[category],inventtrans[itemid],allitems[itemid])

[Syndicate_Admin]

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] )

Add a Column with LOOKUPVALUE.pbix 

[edhans · Accepted solution]

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 

[edhans]

Great @wadda7 - glad I was able to assist. LOOKUPVALUE is something many gravitate towards because users are used to VLOOKUP in Excel, but Power BI works differently and I rarely use LOOKUPVALUE.

[jennratten]

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] )

Add a Column with LOOKUPVALUE.pbix 

[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]

Try taking a look at the pbix that I attached in the reply on Saturday.  That had the working sample. 

[jennratten]

Did you trim and clean the columns in both tables?