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
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"
