Forum Discussion
MMZTM
3 years agoFrequent Visitor
Backwards scheduling
Hey guys, I am looking for an algorithm that will create a backward scheduling for me using the two tables shown below. Based on the delivery date of the order table, the completion dates for the r...
- 3 years ago
Hi, MMZTM load tables into PQ and try this code.
let order = Excel.CurrentWorkbook(){[Name="ORDER_TABLE"]}[Content], operation = Excel.CurrentWorkbook(){[Name="OPERATION_TABLE"]}[Content], order_rec = Record.FromList(order[DELIVERY], List.Transform(order[#"Order-No."], Text.From)), f = (t as table) => [days = List.Buffer(List.Transform(t[Time needed], (x) => Number.From(Splitter.SplitTextByDelimiter(" ")(x){0}))), finish = Record.Field(order_rec, Text.From(t[#"Operation-No."]{0})), gen = List.Generate( () => [i = 0, d = Record.Field(order_rec, Text.From(t[#"Operation-No."]{0}))], (x) => days{x[i]}? <> null, (x) => [i = x[i] + 1, d = Date.AddDays(x[d], - days{x[i]})], (x) => DateTime.Date(x[d]) ), out = Table.FromColumns(Table.ToColumns(t) & {gen}, Table.ColumnNames(t) & {"Completion until"})][out], op_gr = Table.Group(operation, {"Operation-No."}, {{"all", each f(Table.Sort(_, {"Job-No.", Order.Descending}))}}), z = Table.Combine(op_gr[all]), sorted = Table.Sort(z,{{"Operation-No.", Order.Ascending}, {"Job-No.", Order.Ascending}}) in sorted
AlienSx
Super User
3 years agoHi, MMZTM load tables into PQ and try this code.
let
order = Excel.CurrentWorkbook(){[Name="ORDER_TABLE"]}[Content],
operation = Excel.CurrentWorkbook(){[Name="OPERATION_TABLE"]}[Content],
order_rec = Record.FromList(order[DELIVERY], List.Transform(order[#"Order-No."], Text.From)),
f = (t as table) =>
[days = List.Buffer(List.Transform(t[Time needed], (x) => Number.From(Splitter.SplitTextByDelimiter(" ")(x){0}))),
finish = Record.Field(order_rec, Text.From(t[#"Operation-No."]{0})),
gen =
List.Generate(
() => [i = 0, d = Record.Field(order_rec, Text.From(t[#"Operation-No."]{0}))],
(x) => days{x[i]}? <> null,
(x) => [i = x[i] + 1, d = Date.AddDays(x[d], - days{x[i]})],
(x) => DateTime.Date(x[d])
),
out = Table.FromColumns(Table.ToColumns(t) & {gen}, Table.ColumnNames(t) & {"Completion until"})][out],
op_gr = Table.Group(operation, {"Operation-No."}, {{"all", each f(Table.Sort(_, {"Job-No.", Order.Descending}))}}),
z = Table.Combine(op_gr[all]),
sorted = Table.Sort(z,{{"Operation-No.", Order.Ascending}, {"Job-No.", Order.Ascending}})
in
sorted