Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 respective operations are to be scheduled backwards.
Would be great if someone could give me a basic algorithm.
Thanks a lot in advance!
Solved! Go to Solution.
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
Hey AlienSx,
awesome! Works great!
Thanks for the quick reply!
I would still have a basic question:
Would it be possible to write this logic based on the same data in DAX? An iterative solution to this problem seems to make the most sense, right?
If this is not possible I would be super grateful if you could show me an alternative DAX solution for this problem.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.