Assign current row fiscal period to previous but kept the EARLIEST as is

[JustDavid]

I have a table in PQ that shows only [FY Period].

I'd like to achieve the desired result shown as the print screen.

In other words, when [FY Period] is at the EARLIEST (in this case Q1FY25), return the value of [FY_Period].

However, if it's not the EARLIEST, then return the previous [FY_Period]

i.e. when it's Q2FY25, then return Q1FY25.  When it's Q3FY25, then return Q2FY25

[lbendlin · Accepted solution]

Your FY_Period column is in an unusable format - it does not sort well. So you need to add an index column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjR0izQyVYrVATKNEExjCDMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FY_Period = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Desired Result", each #"Added Index"[FY_Period]{List.Max({0,[Index]-1})},type text)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.