Issue with LOOKUPVALUE giving error message

[Gugge]

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 where a single value was expected." I cant understand why this happens. I've tried to change the [Name] column to other columns in that table and some are working and some are getting the error. What am I missing here?

Table called DataCheckNav_vs_SF

My first column is a summarize of our Customer Data in NAV;

NAV_ERPno = SUMMARIZE(

NAV_CustomerData,
NAV_CustomerData[workflowCustomers.number])

Second column get the name from the same table;

NAV_Name2 = LOOKUPVALUE(
NAV_CustomerData[workflowCustomers.name2],
NAV_CustomerData[workflowCustomers.number],
DataCheckNav_vs_SF[NAV_ERPno])

Third column, checks if we have the number in SalesForce;

SF_ERP no =

LOOKUPVALUE(

SF_Account[ERP_ID__c],

SF_Account[ERP_ID__c],

DataCheckNav_vs_SF[NAV_ERPno]

)

My fourth column is supposed to get the name we have in SalesForce by using this formula;

SF_name =

LOOKUPVALUE(

SF_Account[Name],

SF_Account[ERP_ID__c],

DataCheckNav_vs_SF[NAV_ERPno]

)

But it doesn't work.

[v-shex-msft]

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]

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

[DemoFour]

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]

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]

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. 

[smpa01]

@Gugge  SUMMARIZE returns a table. Are you trying to use this to generate a Calculated Column? It is probably giving you the error.

[Gugge]

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 · Accepted solution]

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

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!