Create a custom column in query mode containing the period in format "YYYY-MM" from a date column

[mrg64]

Hello,

In query mode I've created a new custom column containing the period "YYYY-MM" from another date column (InvoiceDelivery Date):

Transacton period=Number.ToText(Date.Year([InvoiceDeliveryDate]))&"-"&Number.ToText(Date.Month([InvoiceDeliveryDate]))

My problem is that I've got 2018-1 as a result instead of 2018-01, 2018-2 instead of 2018-02 and so on.

Later I'm sorting my charts on this period column and it results in 2018-1, 2018-10, 2018-02, 2018-03 and so on. I want it look like 2018-01, 2018-02, 2018-03 ... instead

I've tried to user the "format" command instead but this is not accepted in the query edit mode. got the message Expression.Error: The name 'FORMAT' wasn't recognized. 

TransactionPeriod = FORMAT(Lagerhyra[InvoiceDeliveryDate];"YYYY-MM")

I have to create the new period column during the query since I use the period field in a "group by" command in the same query!

Thanks in advance for all your help!

[v-yuezhe-msft]

@mrg64,

In addition to other's post, you can only use your second formula in DAX. As shown in the following example, you can create a calculated column in Power BI data view.


Regards,
Lydia

[PattemManohar · Accepted solution]

@mrg64 Please try this...

Transaction Period =Text.Combine({Date.ToText(Date.From([InvoiceDeliveryDate]), "yyyy"), "-", Date.ToText(Date.From([InvoiceDeliveryDate]), "MM")})

[mrg64]

Thanks a lot, it works perfect