How to use Lookup command to brand pro_class_desc from sales table to inventory table ?

[admin11]

Hi All

I have post below post 1 week ago :-

https://community.powerbi.com/t5/Desktop/How-to-bring-pro-class-desc-field-from-sales-table-to-inven...

I got below expression :-

Column =

LOOKUPVALUE(

'SALES'[pro_class_desc],

'SALES'[stock_code], 'INVC'[stock_code],

'SALES'[pro_class], 'INVC'[pro_class]

)

i get the error :-

Function 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

My PBI file :-

https://www.dropbox.com/s/d3eknkrdd05xovg/PBT_V2021_333%20bring%20product%20class%20desc%20to%20inve...

Paul

[HotChilli]

It just means that more than 1 'SALES'[pro_class_desc] are being returned for the same search fields.  Either the data is wrong - totally different pro_class_desc or slightly different spelling or something -  or you're going to have to take a decision about which pro_class_desc is correct 

[admin11]

@HotChilli 

I think I have to use another approach 

thank you for sharing 

paul

[HotChilli · Accepted solution]

First attempt to fix the code- Change the datatype of INVC[stock code] to text. Gets rid of the error.  Doesn't bring back many matches though.

Second attempt - Change code to 

Column2 =

LOOKUPVALUE(

'SALES'[pro_class_desc],

'SALES'[pro_class], 'INVC'[pro_class]

)

because it doesn't look like the stock code match is required.  More matches but still not great.

Third attempt - doesn't 'Drawwing Number' field in INVC have the data anyway?  It's a badly named field.

--------------------------

Anyway, you know your data best so I'll let you investigate.

[admin11]

@HotChilli 

When i load only one company data , your expression working fine :-

When i try to put your expression into my actual PBI file , i get error :-

Link By pro_class :-

Link using Stock Code :-

Any suggestion ?

Paul