0

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.
Improve this question

2 Answers 2

Reset to default
2

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

Improve this answer
3
  • Thank you for the answer. Could you please tell me, in the Power BI Desktop, how do we know when we should be using DAX or M?
    – nilsinelabore
    Commented Apr 7, 2021 at 4:15
  • Maybe you want to do some training or watch a few tutorials, so you get a better understanding of what the moving parts in Power BI are. As I already wrote in my answer: M code is the language for Power Query. It's what you need to use inside the queries that you generate in the Query Editor. If you don't know how to determine if you're in the Query Editor, then you really need to get some basic down first. DAX is only used in modelling, i.e. working with data that is already in the model.
    – teylyn
    Commented Apr 7, 2021 at 9:19
  • 1
    take a look at stackoverflow.com/questions/39632894/…
    – teylyn
    Commented Apr 7, 2021 at 9:26
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 ))
Improve this answer
2
  • Hi thank you for the solution, I tried Date.ToText( table_name[Timestamp, "MMM" ) & "_" & Text.From(Date.Year( table_name[Timestamp )) in Transform data/Custom Column but there was an error about 'MMM'. While code FORMAT( table_name[Timestamp], "MMM") & "_" & FORMAT(table_name[Timestamp] ,"yyyy") caught error Expression.Error: The name 'FORMAT' wasn't recognized. Make sure it's spelled correctly. Did I use it incorrectly?
    – nilsinelabore
    Commented Apr 7, 2021 at 4:44
  • 2
    That error shows when you use DAX commands in the Power Query Editor. You cannot use DAX functions in Power Query. These are two different languages and you cannot simply mix them up.
    – teylyn
    Commented Apr 7, 2021 at 9:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.