How do I add a new column based on values of another column in Power Query?

I have a table that contains sales data over the course of several years on a fiscal calendar. Here is a snippet example of this data:

I would like to add another column (PY Sales) to see how we compare to the previous year. Here is an example:

I have seen a few similar questions here but most regard columns recognized as a date, which these are not. I can't use mathematical calculations because the date data are string type. I need the data to match up specifically on the same Location, Fiscal Year, Period, and Week, and Item as additional items or sales locations might be added over time.

How can I add the PY Sales column using Power Query, conditional on the value of these other columns?

Create a column that contains the numerical part of the year, minus one, such that FY25 become FY24. Merge the table onto itself again, matching the newly adjusted Fiscal Year column to the old Fiscal Year column, and any other additional columns to themselves

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "OffsetYear", each "FY" & Text.From(Number.From(Text.End([Fiscal  Year],2))-1)),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"OffsetYear", "Item", "Location", "Week"},#"Added Custom" , {"Fiscal  Year", "Item", "Location", "Week"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Sales"}, {"Sales.1"})
in
    #"Expanded Table"

Another example. Item #001 only exists in FY24 and #004 only exists in 2025. We remove year and sales, and create all combinations of the rows for each year, then repeat the exercise above

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Fiscal Year", "Sales"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Fiscal Year", each List.Distinct(Source[Fiscal Year])),
    #"Expanded FiscalYear" = Table.ExpandListColumn(#"Added Custom", "Fiscal Year"),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded FiscalYear", {"Location", "Fiscal Year", "Week", "Item"}, Source, {"Location", "Fiscal Year", "Week", "Item"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries1", "Table2", {"Sales"}, {"Sales"}), // we now have all combinations of unique rows for each year

    #"Added Custom2" = Table.AddColumn(#"Expanded Table2", "OffsetYear", each "FY" & Text.From(Number.From(Text.End([Fiscal Year],2))-1)),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom2", {"OffsetYear", "Item", "Location", "Week"},#"Added Custom2" , {"Fiscal Year", "Item", "Location", "Week"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Sales"}, {"LYSales"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Table1", "YOY", each Number.From([Sales]??0) - Number.From([LYSales]??0) ),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each not ([Sales] =null and [LYSales] = null)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,0,Replacer.ReplaceValue,{"Sales"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"YOY", type number}, {"LYSales", type number}, {"Sales", type number}})
    in  #"Changed Type"