CONVERT FY format to exact date in Power BI

Question

I have a string formatted date field which says "FY23Q1", now assuming the fiscal year starts in October and ends in September, I want to recode the string into 10/01/2022. ie the first date of the month

INPUT Expected output: FY23Q1 10/01/2022 FY23Q2 01/01/2023 FY23Q3 04/01/2023

Answer 1

Add a new custom column in PQ and paste in the following code. Make sure [Column1] refers to your column with FY23Q1 date in it.

let 
year = Number.From(Text.Middle([Column1], 2,2)),
quarter = Number.From(Text.Middle([Column1], 5,6)),
startDate = Date.AddQuarters( #date(year-1+2000, 10, 1), quarter - 1)


in startDate

Full working example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcos0Mg40VIrVgTKNEExjBNMExjQBqY0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let 
year = Number.From(Text.Middle([Column1], 2,2)),
quarter = Number.From(Text.Middle([Column1], 5,6)),
startDate = Date.AddQuarters( #date(year-1+2000, 10, 1), quarter - 1)


in startDate)
in
    #"Added Custom"