cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
chaladc Frequent Visitor
Frequent Visitor

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. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Convert Year Quarter format into date format

Hi @chaladc,

 

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

View solution in original post

1 REPLY 1
Super User
Super User

Re: Convert Year Quarter format into date format

Hi @chaladc,

 

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

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)