Forum Discussion
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.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
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.
5 Replies
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
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])
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") )
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 🙂
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.
