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

     

     

DemoFour's avatar
DemoFour
Continued Contributor
5 years ago

Hi Gugge

Would this not be eaiser in the query editor, where you can merge and create tables with the required columns a bit eaiser. 
 

Gugge's avatar
Gugge
Helper I
5 years ago

Hi, thank you for the answer. It might be easier, but I do not have the knowledge on how to solve that with a query.

  • DemoFour's avatar
    DemoFour
    Continued Contributor
    5 years ago

    Morning Gugge 

     

    It is very easy to do this in the Query Editor, Merging tables and choosing your columns, if you do not have a unique ID for these, you can also create one. 

    LOOKUPVALUE often gives that message when it scans the table and it is not clear which result it can find as there are several options. 



    If you want to find more about this, try these videos. 

    SQLBI introducing lookupvalue 

    SQLBI Understanding lookupvalue

     





    If you want some help with the Query way, I would be happy to guide you if you provide some dummy data.