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
