Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Concatenate lookupvalue type errror

I am trying to concatenate 2 lookupvalues in order to get a unique key for every single planner per day. I am doing that by concatenating the DayNumber + UserNumber. This is what the calculated column looks like now. 

 

plannerID = 
VALUE(
   CONCATENATE(
      LOOKUPVALUE(
         table1[DayNumber], 
         table1[DayName], 
         DAY(table2[Date])
      ),
      LOOKUPVALUE(
         table3[userNumber],
         table3[user]
         table2[creationUser]
      )
   )
)

 

table1[DayNumber] = int 

table1[DayName] = string

table2[Date] = Date but it takes the DAY()

 

table3[UserNumber] = int
table3[user] = sting

table2[creationUser] = int


The only problem is now it gives me the error: "in the function LOOKUPVALUE is being compared to type text with values of type integer. Consider the use of VALUE or FORMAT to convert the values." But i'm already using VALUE. I'd appreciate some help. Thanks in advance

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

     

    You need to convert the search value to text as the search column is text in both lookups:

    plannerID = 

   CONCATENATE(
      LOOKUPVALUE(
         table1[DayNumber], 
         table1[DayName], 
         FORMAT(DAY(table2[Date]),"@")
      ),
      LOOKUPVALUE(
         table3[userNumber],
         table3[user],
         FORMAT(table2[creationUser],"@")
      )
   )

    ________________________

    If my answer was helpful, please click Accept it as the solution to help other members find it useful

    Click on the Thumbs-Up icon if you like this reply 🙂


    Website YouTube  LinkedIn

1 Reply

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

     

    You need to convert the search value to text as the search column is text in both lookups:

    plannerID = 

   CONCATENATE(
      LOOKUPVALUE(
         table1[DayNumber], 
         table1[DayName], 
         FORMAT(DAY(table2[Date]),"@")
      ),
      LOOKUPVALUE(
         table3[userNumber],
         table3[user],
         FORMAT(table2[creationUser],"@")
      )
   )

    ________________________

    If my answer was helpful, please click Accept it as the solution to help other members find it useful

    Click on the Thumbs-Up icon if you like this reply 🙂


    Website YouTube  LinkedIn