Create new quarterly record with previous quarter data with conditions

Hello

Please try this in the Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTEk1VNJRMjIwtARSgaWJRSWpRQrGQLaJgYmhUqwObkVGYEXGlngVGUIUWYAVOScWpxphN8jI0ACfEojJpviUmICEjICmxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Class = _t, Fiscal_Year = _t, Fiscal_Quarter = _t, Q_RunTot_Conn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Class", type text}, {"Fiscal_Year", Int64.Type}, {"Fiscal_Quarter", type text}, {"Q_RunTot_Conn", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Class", "Fiscal_Year"}, {{"Data", each _, type table [Class=text, Fiscal_Year=number, Fiscal_Quarter=text, Q_RunTot_Conn=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let t = Table.AddColumn([Data],"Quarter",each Text.Replace([Fiscal_Quarter],"Quarter ",""))
in 
Table.AddColumn(Table.ExpandListColumn(
Table.AddColumn(t,"LastQ",each 

if [Quarter]<>"4" then 
List.Select({
    Int64.From([Quarter]),
    let temp  = Int64.From([Quarter])+1
    in 
    if Table.RowCount(Table.SelectRows(t,each Int64.From([Quarter]) = temp))=0
    then Int64.From([Quarter])+1
    else null
},each _<>null) else {Int64.From([Quarter])}
), "LastQ"),"New Quarter",each "Quarter "&Text.From([LastQ]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Q_RunTot_Conn", "New Quarter"}, {"Q_RunTot_Conn", "Fiscal_Quarter"})
in
    #"Expanded Custom"

The output shows:

Here is my test pbix file:

pbix

I hope this helps.

Best regards

Giotto Zhi