Error with LOOKUPVALUE

[Anonymous]

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.28	jun. 2021

The second one looks like this:

Date (String and Int Type)

31/03/2021

30/04/2021

31/05/2021

30/06/2021

I am trying to create a second column in Table2 with

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

But with this line I just obtain blank cells.

What would be the correct way to do this LOOKUPVALUE?

Thanks!!

[v-chenwuz-msft · Accepted solution]

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]

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]

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]

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 🙂

[PC2790]

Hey @Anonymous ,

For LOOKUPVALUE to work, the columns from two tables should have exact match.

You should look for fuzzy matching concept in power query to cater to your requirement here.

How fuzzy matching works in Power Query - Power Query | Microsoft Docs

Fuzzy Matching in Power BI and Power Query; Match based on Similarity Threshold - RADACAD