Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Error with LOOKUPVALUE

Hey everyone!   I have two tables. The first one looks like this: Count (Float Type) MonthInCalendar ( Date Type) 11016.26 mar. 2021 22750.6 apr. 2021 24028.95 may. 2021 26069.2...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    Please try this code to do that.

    Column = 
LOOKUPVALUE (
    Table1[Count (Float Type)],
    Table1[MonthInCalendar (String Type)], FORMAT ( 'Table2'[Date (Date type)], "mmm. yyyy" )
)

     

    Result:

     

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

SolomonovAnton's avatar
SolomonovAnton
Super User
4 years ago

hello !

 

in The [Table1] table your field MonthInCalendar  has Date Type. It mean what real data in The column 'MonthInCalendar' it's not 'mar. 2021' it is '03/01/2021' or '03/15/2021' or '03/31/2021' Your formate type show it in "MM.YYYY"

so your scipt should looks like 

 

 

Column = LOOKUPVALUE('Table1'[Count],'Table1'[MonthInCalendar], Table2[Date])

 

 

 

and in The [Table2] The column [Date] should be Date type (it mean same data type as The [MonthInCalendar] field in The [Table1])

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hey! My bad, i wrote wrong the data types. In Table1, the datatype is String and Int, because of how I did the transformation and grouping of that table. In Table2 it is a Date type. If I use your solution it gives me the following error: 

Function 'LOOKUPVALUE' does not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

 

To get the MonthInCalendar column I used this line:

= Table.AddColumn( Origen, "MonthInCalendar", each Date.ToText([Item Date], "MMM yyyy") )

 

 

  • SolomonovAnton's avatar
    SolomonovAnton
    Super User
    4 years ago

    your fields in calculate column

    Column = LOOKUPVALUE('Table1'[Count],'Table1'[MonthInCalendar], Table2[Date])

    should be the same type and same view so it mean for example 

    'Table1'[MonthInCalendar] should be text type and format "MMM YYYY"

    and 

    Table2[Date]  should be text type and format "MMM YYYY"

     

     

    if  I have resolved you qestion please mark topic as resolved 

    if I helped you please click to give Kudos for me 🙂