Search code examples
powerbipowerquerym

Trying to convert year quarter 2Q03 format column values to date in Power Bi

I am a newbie to power BI and trying to convert Year Quarter column which is in 2Q03 to either 30/06/2003 or 2003 Q2. Much appreciated for the help in advacne.

Solution

  • The latter format is a bit easier. You can use Text.Start and Text.End in a custom column to achieve this:

    "20" & Text.End([String], 2) & " Q" & Text.Start([String], 1)

    This takes the right two characters as the year and the left one as the quarter.

    For the former format, similar logic, but you use #date(yyyy,mm,dd) instead of joining strings:

    Date.EndOfQuarter(
    #date(
       2000 + Number.From(Text.End([String], 2)),
       3 * Number.From(Text.Start([String], 1)),
       1)))