Forum Discussion

Gugge's avatar
Gugge
Helper I
5 years ago
Solved

Issue with LOOKUPVALUE giving error message

Hi, I'm trying to make a simple table with data from other tables with SUMMARIZE and LOOKUPVALUE. But when using that LOOKUPVALUE-formula, I'm getting the "A table of multiple values was supplied wh...
Need Help
  • smpa01's avatar
    smpa01
    5 years ago

    Guggemy best guess is SF_Account[Name] has multiple values against each unique SF_Account[ERP_ID__c].

     

    Error Reproduced here

     

    /* table name _left*/
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
    #"Changed Type"
    /* table name _right*/
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsgwMwGwnINsIzHIGsoyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ValA = _t, ValB = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ValA", type text}, {"ValB", Int64.Type}})
in
    #"Changed Type"

     

     

    Now, try this as calculated column in _left. You will see the issue.

     

    Column = LOOKUPVALUE(_right[ValB],_right[ValA],_left[Column1])

     

     

Gugge's avatar
Gugge
Helper I
5 years ago

 

Thanks you for your answer.

This is  how it looks. I've created a table by using SUMMARIZE, then I want to expand the table to be able to cross check information. The first three LOOKUPVALUE columns work, I added in one just to show what happens. As seen in the picture, when there is no result it returns a blank cell and when it is a result it returns the data. But for the Name column, all I'm getting is #ERROR and the message "A table of multiple values was supplied where a single value was expected."

 

SF_city =
LOOKUPVALUE(
SF_Account[BillingCity],
SF_Account[ERP_ID__c],
DataCheckNav_vs_SF[NAV_ERPno]
)
 
SF_name =
LOOKUPVALUE(
SF_Account[Name],
SF_Account[ERP_ID__c],
DataCheckNav_vs_SF[NAV_ERPno])
smpa01's avatar
smpa01
Community Champion
5 years ago

Guggemy best guess is SF_Account[Name] has multiple values against each unique SF_Account[ERP_ID__c].

 

Error Reproduced here

 

/* table name _left*/
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
    #"Changed Type"
/* table name _right*/
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsgwMwGwnINsIzHIGsoyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ValA = _t, ValB = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ValA", type text}, {"ValB", Int64.Type}})
in
    #"Changed Type"

 

 

Now, try this as calculated column in _left. You will see the issue.

 

Column = LOOKUPVALUE(_right[ValB],_right[ValA],_left[Column1])

 

 

  • Gugge's avatar
    Gugge
    Helper I
    5 years ago

    Hi,

    Recreated this and you are correct. I have some duplicate values in my SF_Account column ERP_No. Removed them from my excel test I posted here and it works.

     

    Still strange that some columns works and some doesn't, but now I know where to start when solving this. Thank you!