Date and Month Format

[Dev-13]

Hello,

I am looking to format the date in my calendar table as 2023M01 so in the example 2023 is the year plus M for month and 01 for the month. The syntax I have in my calendar is (100*year([date])+month([date])) This returns 202301 but I need to include the text M to give me the full requirement so 2023M01 I've tried adding in "M" in-between the year and month but it's not giving me the correct result. What syntax would work for this please?

[BA_Pete · Accepted solution]

Hi @Dev-13 ,

Try this as a new custom column:

Text.Combine(
    {
        Text.From(Date.Year([date])),
        "M",
        Text.PadStart(Text.From(Date.Month([date])), "0", 2)
    }
)

Pete

[Rickmaurinus · Accepted solution]

Hi Dev-13,

The easiest way to achieve this is by making use of the Date.ToText function. 

 Date.ToText( [Date], [Format = "yyyy'M'MM"] )

 The formatting strings allow you to turn a date (in the [Date] column) into your desired custom output. For the different possible output strings, take a look at : https://powerquery.how/date-totext/

There's an extensive list and image with example format strings. 

Regards,

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[Dev-13]

Thank you @Rickmaurinus This was a simple solution which worked well. 

[BA_Pete · Accepted solution]

Hi @Dev-13 ,

Try this as a new custom column:

Text.Combine(
    {
        Text.From(Date.Year([date])),
        "M",
        Text.PadStart(Text.From(Date.Month([date])), "0", 2)
    }
)

Pete