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

     

     

9 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    5 years ago

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

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

         

         

  • 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. 

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