Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Convert Year Quarter format into date format

I am newbiee to PowerBI and trying to convert year quarter 1Q06 format to either date format (03/30/2006) or 2006 Q1 format by using power query.   
Need Help
  • Nolock's avatar
    Nolock
    7 years ago

    Hi Anonymous,

     

    to format from 1Q06 to 2016 Q1 use:

    let
    Source = "1Q06",
    QuarterNr = Text.BeforeDelimiter(Source, "Q"),
    Year = "20" & Text.AfterDelimiter(Source, "Q"),
    NewFormat = Year & " Q" & QuarterNr
in
    NewFormat

    And to convert from 1Q06 to the last day of a quarter use:

    let
    Source = "1Q06",
    QuarterNr = Number.FromText(Text.BeforeDelimiter(Source, "Q")),
    Year = Number.FromText("20" & Text.AfterDelimiter(Source, "Q")),
    StartOfYear = #date(Year, 1, 1),
    EndOfQuarter = Date.AddDays(Date.AddQuarters(StartOfYear, QuarterNr), -1)
in
    EndOfQuarter
Nolock's avatar
Nolock
Resident Rockstar
7 years ago

Hi Anonymous,

 

to format from 1Q06 to 2016 Q1 use:

let
    Source = "1Q06",
    QuarterNr = Text.BeforeDelimiter(Source, "Q"),
    Year = "20" & Text.AfterDelimiter(Source, "Q"),
    NewFormat = Year & " Q" & QuarterNr
in
    NewFormat

And to convert from 1Q06 to the last day of a quarter use:

let
    Source = "1Q06",
    QuarterNr = Number.FromText(Text.BeforeDelimiter(Source, "Q")),
    Year = Number.FromText("20" & Text.AfterDelimiter(Source, "Q")),
    StartOfYear = #date(Year, 1, 1),
    EndOfQuarter = Date.AddDays(Date.AddQuarters(StartOfYear, QuarterNr), -1)
in
    EndOfQuarter