locked
Request : Update Date.ToText function to generate Quarter of the Year : Date.ToText([DischargeDate],"yyyy-Qq") RRS feed

  • Question

  • HI ... thanks so much for Power Query and the M Language.

    It would be great if Date.ToText were updated to display the Quarter of the Year.

    Currently, I have to use a formula like this to create Quarter of the Year Text column

    = Date.ToText([DischargeDate],"yyyy") & "-Q" & Number.ToText(Date.QuarterOfYear([DischargeDate]))

    I would love to use something like

    =  Date.ToText([DischargeDate],"yyyy-Qq") to create a text value of "2016-Q1"

    I create this Year-Quarter of the Year column at least once in almost every workbook that I use the Power Query ETL magic.

    Thanks again ... Charles.

    Wednesday, August 31, 2016 6:03 AM

Answers

  • Hi Charles,

    It's unlikely we would support this directly in the format string. However, you could easily define your own function (called Date.ToQuarterText) to encapsulate this commonly used operation.

    First, create a query and call it Date.ToQuarterText. Define it using the following formula:

    = (date) => Date.ToText(date,"yyyy") & "-Q" & Number.ToText(Date.QuarterOfYear(date))

    Then, whenever you want to apply your quarter formatting, just call it like so:

    =  Date.ToQuarterText([DischargeDate])

    Would that work for you?

    Ehren

    Friday, September 2, 2016 9:31 PM