Forum Discussion

MMZTM's avatar
MMZTM
Frequent Visitor
3 years ago
Solved

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...
  • AlienSx's avatar
    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