Creating a new table from other queries

Hi all, proving that AI can't help me with everything, I'm in need of some SME knowledge about Power Query, and some expert coding advice.

I need to create a forecast table from some existing queries, and while ChatGPT has got me so far, I'm having trouble wi the last piece of the puzzle. Without getting too in the weeds: Below is a query that works fine to create a table that represents a forecast from a table called pm and pmsequence, returning a correct forecast of pm records. When I want to add some complexity, I can get a query that returns the first part correctly, but not the second.

The logic I am trying to follow to create my table called Forecast:

Create a new table called Forecast, with the following columns. pmnum, pmcounter, due_date, jpnum, jpcost, location, and assetnum.

For each row of the pm table, insert a row and  return:
pmnum = pm.pmnum,
pmcounter =pm.pmcounter value +1
due_date= if pm.extdate <> null then pm.extdate, else nextdate
jpnum = check if the current pm.pmnum is in the pm.sequence table. If it is, divide the forecast.pmcounter by highest pmsequence.interval number. If the result is a whole number use the corresponding row pmsequence.jpnum, if not, use the next highest interval until you get a whole number. if no interval number returns a whole number, use the pm.jpnum value.
jpcost = return the mrp_totalcost value for the forecast.jpnum value from the jobplan table
location = pm.location, if null return null

assetnum = pm.assetnum, if null return null

Then - If the pm row has a value in the pm.route field, insert additional rows under the current Forecast row, corresponding to the number of rows for the route number in the route_stop table.
for each of these rows return:

pmnum = pm.pmnum,
pmcounter = current forecast.pmcounter value
due_date= if pm.extdate <> null then pm.extdate, else nextdate
jpnum = if route_stop.jpnum <> null then route_stop.jpnum else pm.jpnum
jpcost = return the mrp_totalcost value for the forecast.jpnum value from the jobplan table
location = route_stop.location, if null return null

assetnum = route_stop.assetnum, if null return null

Once its done that  for the current pmcounter value, increment it by one and insert the rows again as per above, until the due_date falls outside the forecast range, which is 10 years.
Once the current pm row is finshed, move the the next pm row.


The following code works to return a table for all pm values with the right counters, but without the added complexity of the route_stop, and the second copy of the code return the right pm and route_stop information, but stops after the first pmcoutner value. 

If anyone knows how to merege these two to get he right logic, or knows a better way to do the forecast table, I'd be stoked to hear!!!!!

Working pm forecast

let

    // Reference the "pm" query

    Source_pm = pm,

    // Reference the "pmsequence" query

    Source_pmsequence = pmsequence,

    // Reference the "jobplan" query

    Source_jobplan = jobplan,

    // Define the forecast range (today's date + 10 years)

    Today = DateTime.Date(DateTime.LocalNow()),

    ForecastEndDate = Date.AddYears(Today, 10),

    // Define a function to add frequency to a date

    AddFrequency = (startDate as date, frequency as number, unit as text) as date =>

        let

            newDate =

                if unit = "DAYS" then Date.AddDays(startDate, frequency)

                else if unit = "WEEKS" then Date.AddWeeks(startDate, frequency)

                else if unit = "MONTHS" then Date.AddMonths(startDate, frequency)

                else if unit = "YEARS" then Date.AddYears(startDate, frequency)

                else startDate

        in

            newDate,

    // Function to find the appropriate "jpnum"

    FindJpnum = (pmCounter as number, sortedTable as table) as nullable text =>

        let

            // Get the list of intervals and jpnums

            Intervals = sortedTable[interval],

            Jpnums = sortedTable[jpnum],

            // Find the appropriate jpnum based on the logic

            ResultJpnum = if Table.RowCount(sortedTable) = 1 then

                              sortedTable{0}[jpnum]

                          else

                              List.First(List.Select(List.Zip({Intervals, Jpnums}), each Number.Mod(pmCounter, _{0}) = 0)){1}

        in

            ResultJpnum,

    // Function to generate forecast rows

    GenerateForecastRows = (pmRecord as record, sourcePmsequence as table, sourceJobplan as table, forecastEndDate as date) as table =>

        let

            PmNum = pmRecord[pmnum],

            InitialCounter = pmRecord[pmcounter] + 1,

            InitialDueDate = if pmRecord[extdate] <> null then pmRecord[extdate] else pmRecord[nextdate],

            Frequency = pmRecord[frequency],

            Unit = pmRecord[frequnit],

            PmLocation = pmRecord[location],

            PmAsset = if pmRecord[assetnum] <> null then pmRecord[assetnum] else null,

            // Initialize the forecast rows

            ForecastRows = List.Generate(

                () => [CurrentPmCounter = InitialCounter, CurrentDueDate = InitialDueDate],

                each [CurrentDueDate] <= forecastEndDate,

                each [CurrentPmCounter = [CurrentPmCounter] + 1, CurrentDueDate = AddFrequency([CurrentDueDate], Frequency, Unit)],

                each

                    let

                        FilteredPmsequence = Table.SelectRows(sourcePmsequence, each [pmnum] = PmNum),

                        SortedPmsequence = Table.Sort(FilteredPmsequence, {{"interval", Order.Descending}}),

                        Jpnum = FindJpnum([CurrentPmCounter], SortedPmsequence),

                        JpCost = if Jpnum <> null then

                                    try Table.SelectRows(sourceJobplan, each [jpnum] = Jpnum){0}[mrp_totalcost]

                                    otherwise 0

                                 else 0

                    in

                        [pmnum = PmNum, pmcounter = [CurrentPmCounter], due_date = [CurrentDueDate], jpnum = Jpnum, jpcost = JpCost, location = PmLocation, asset = PmAsset]

            ),

            // Convert the list to a table

            ForecastTable = Table.FromRecords(ForecastRows)

        in

            ForecastTable,

    // Generate the forecast rows for each "pm" record

    ForecastTables = List.Transform(Table.ToRecords(Source_pm), each GenerateForecastRows(_, Source_pmsequence, Source_jobplan, ForecastEndDate)),

    // Combine all forecast tables

    CombinedForecastTable = Table.Combine(ForecastTables),

    // Convert jpcost to Currency type and replace nulls with 0

    CombinedForecastTableCurrency = Table.TransformColumnTypes(

        Table.ReplaceValue(CombinedForecastTable, null, 0, Replacer.ReplaceValue, {"jpcost"}),

        {{"jpcost", Currency.Type}}

    )

in

    CombinedForecastTableCurrency

Almost working route_stop

let
// Reference the "pm" query
Source_pm = pm,

// Reference the "pmsequence" query
Source_pmsequence = pmsequence,

// Reference the "jobplan" query
Source_jobplan = jobplan,

// Reference the "route_stop" query
Source_route_stop = route_stop,

// Define the forecast range (today's date + 10 years)
Today = DateTime.Date(DateTime.LocalNow()),
ForecastEndDate = Date.AddYears(Today, 10),

// Define a function to add frequency to a date
AddFrequency = (startDate as date, frequency as number, unit as text) as date =>
let
newDate =
if unit = "DAYS" then Date.AddDays(startDate, frequency)
else if unit = "WEEKS" then Date.AddWeeks(startDate, frequency)
else if unit = "MONTHS" then Date.AddMonths(startDate, frequency)
else if unit = "YEARS" then Date.AddYears(startDate, frequency)
else startDate
in
newDate,

// Function to find the appropriate "jpnum"
FindJpnum = (pmCounter as number, sortedTable as table) as nullable text =>
let
// Get the list of intervals and jpnums
Intervals = sortedTable[interval],
Jpnums = sortedTable[jpnum],
// Find the appropriate jpnum based on the logic
ResultJpnum = if Table.RowCount(sortedTable) = 1 then
sortedTable{0}[jpnum]
else
List.First(List.Select(List.Zip({Intervals, Jpnums}), each Number.Mod(pmCounter, _{0}) = 0)){1}
in
ResultJpnum,

// Function to generate forecast rows
GenerateForecastRows = (pmRecord as record, sourcePmsequence as table, sourceJobplan as table, sourceRouteStop as table, forecastEndDate as date) as table =>
let
PmNum = pmRecord[pmnum],
InitialCounter = pmRecord[pmcounter] + 1,
InitialDueDate = if pmRecord[extdate] <> null then pmRecord[extdate] else pmRecord[nextdate],
Frequency = pmRecord[frequency],
Unit = pmRecord[frequnit],

// Initialize the base forecast row
BaseForecastRow = [
pmnum = PmNum,
pmcounter = InitialCounter,
due_date = InitialDueDate,
jpnum = null,
jpcost = null,
location = pmRecord[location],
assetnum = pmRecord[assetnum]
],

// Filter route_stop for the current route
RouteStops = if pmRecord[route] <> null then Table.SelectRows(sourceRouteStop, each [route] = pmRecord[route]) else null,

// Function to transform route_stop rows
TransformRouteStops = (routeStopRecord as record) as record =>
let
Jpnum = if routeStopRecord[jpnum] <> null then routeStopRecord[jpnum] else FindJpnum(InitialCounter, sourcePmsequence),
JpCost = try Table.SelectRows(sourceJobplan, each [jpnum] = Jpnum){0}[mrp_totalcost] otherwise 0
in
[
pmnum = PmNum,
pmcounter = InitialCounter,
due_date = InitialDueDate,
jpnum = Jpnum,
jpcost = JpCost,
location = routeStopRecord[location],
assetnum = routeStopRecord[assetnum]
],

// Generate the list of forecast rows
ForecastRows = List.Accumulate(
{BaseForecastRow} & List.Buffer(Table.ToRecords(RouteStops)),
{},
(state, current) => state & List.Buffer({TransformRouteStops(current)})
)
in
Table.FromRecords(ForecastRows),

// Generate the forecast rows for each "pm" record
ForecastTables = List.Transform(Table.ToRecords(Source_pm), each GenerateForecastRows(_, Source_pmsequence, Source_jobplan, Source_route_stop, ForecastEndDate)),

// Combine all forecast tables
CombinedForecastTable = Table.Combine(ForecastTables),

// Convert jpcost to Currency type and replace nulls with 0
CombinedForecastTableCurrency = Table.TransformColumnTypes(
Table.ReplaceValue(CombinedForecastTable, null, 0, Replacer.ReplaceValue, {"jpcost"}),
{{"jpcost", Currency.Type}}
)
in
CombinedForecastTableCurrency