CODEWITHSUNDEEP
powerbipowerquery
nilsinelabore
nilsinelabore

Reputation: 5145

Expression.Error: The name 'CONCATENATE' wasn't recognized. Make sure it's spelled correctly

I want to extract month of year from datetime timestamp and create a new column in Power BI where I use DirectQuery.

I tried:

CONCATENATE(MONTH([Timestamp]),YEAR([Timestamp]))

and caught error:

Expression.Error: The name 'CONCATENATE' wasn't recognized.  Make sure it's spelled correctly.

According to Microsoft Docs Date.Month seems to work but it only extracts month data instead of year-month, there are other options such as WeekOfYear or WeekOfMonth but I could not find MonthOfYear?

I probably shouldn't use this as I found out that this is not suitable for Power Query(which I think need M formula language?) but just testing out:

FORMAT(table_name[Timestamp],"mmm-yyyy")

and caught error:

Expression.Error: The name 'FORMAT' wasn't recognized.  Make sure it's spelled correctly.

Upvotes: 0

Views: 6669

Answers (2)

teylyn
teylyn

Reputation: 35990

You need to straighten out whether you want to use Power Query or DAX. CONCATENATE() is DAX, whereas Date.Month is M, i.e. Power Query. Each is used in a different environment: Power Query to retrieve data from data source, DAX for modelling.

If you use DAX in the Power Query editor, then of course it's not recognised.

So, with DAX, use CONCATENATE() enter image description here

and in Power Query use M code

enter image description here

Upvotes: 2

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

If you want to do it in DAX by creating a custom column :

MMM_YYYY = FORMAT(  table_name[Timestamp],      "MMM") & "_" & FORMAT(  table_name[Timestamp]  ,"yyyy")

Using Power Query:

=Date.ToText( table_name[Timestamp,  "MMM" ) & "_" & Text.From(Date.Year( table_name[Timestamp ))

Upvotes: 1

Related Questions