CODEWITHSUNDEEP
powerbidax
nilsinelabore
nilsinelabore

Reputation: 5095

Extract year-month from datetime in Power BI

I would like to create a new column in the transform data page, where datetime data is converted to year-month. I used:

Format('df'[Date], "YYYY-MM")

and it returned:

Token Literal expected. Show error.

Date is in this format: 10/12/2015

Expected output: 12-2015

Besides, I was able to create this column in the Data section but as I open Transform in the top toolbar as I would like to groupby and aggregate data, the newly created column is not in the table.

Update:

Tried different spelling for format and it returned:

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

But it had stated no error detected for that particular custom column.

Upvotes: 0

Views: 5851

Answers (1)

Murray Foxcroft
Murray Foxcroft

Reputation: 13745

When you create the column in the table (next to the visuals) in PowerBI you are using the DAX programming language. This is the "modelling" side of PowerBI. You can create your DAX calculated column as:

=FORMAT('df'[Date],"MM-yyyy")

When you use "Transform Data", then you are in the ingestion side of PowerBI and you use M language from PowerQuery. Make sure the type of your date column is correct before you apply the formula, if it is not, you can right click the column -> Change Type -> Date.

You can get your date formatted using the below (see the advanced editor). Docs here:

let
    Source = fn_GenerateDates(#date(2010, 1, 1), #date(2030, 1, 1), null),
    #"Added Custom" = Table.AddColumn(Source, "MyCustomFormattedDate", each Date.ToText([Date], "yyyy-MM"))
in
    #"Added Custom"

Or just use the Add Column->Add Custom Column option on the toolbar:

enter image description here

Upvotes: 2

Related Questions