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

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Gugge,

Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

Gugge's avatar
Gugge
Helper I
5 years ago

Hi,

I recreated a excel file that gives me the same error, I'm unsure on how to upload  a file here so i uploaded it to a file sharing site. https://ufile.io/hnbqmwg0 
All the data is fake, but it gives me the same error when trying to create the table with the below formulas, the first three works but the last one gives the error "A table of multiple values was supplied where a single value was expected."

DataCheckNav_vs_SF = 
            SUMMARIZE
                (NAV_CustomerData,
                NAV_CustomerData[NAV_FAKEno])
NAV_Name2 = LOOKUPVALUE(
    NAV_CustomerData[NAV_Fakename2],
    NAV_CustomerData[NAV_FAKEno],
    DataCheckNav_vs_SF[NAV_FAKEno]
    )
SF_ERP no = 
    LOOKUPVALUE(
            SF_Account[SFFAKE_ERPID],
            SF_Account[SFFAKE_ERPID],
            DataCheckNav_vs_SF[NAV_FAKEno]
            )
SF_name = 
    LOOKUPVALUE(
            SF_Account[SFFAKE_name],
            SF_Account[SFFAKE_ERPID],
            DataCheckNav_vs_SF[NAV_FAKEno]
            )