Forum Discussion

Dev-13's avatar
Dev-13
Helper I
3 years ago
Solved

Date and Month Format

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?

General Comment
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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's avatar
    Rickmaurinus
    3 years ago

    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.

3 Replies

  • Rickmaurinus's avatar
    Rickmaurinus
    Helper V
    3 years ago

    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.

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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