Forum Discussion
Aggregate in numerical order
Hi Anonymous, why not to use Power Query solution? If you don't know how to use this query - read note at the botom of my post.
I've added 3 more rows to your sample data just to demonstrate that we consider every Order # separately.
EDIT: 2024-03-28 15:07 GMT+1: I've edited my code, and now it is realy fast also with big data
Result
let fn_AggLT = (lst as list)=> List.Reverse(List.Generate( ()=> [ x = List.Count(lst)-1, y = lst{x} ], each [x] >= 0, each [ x = [x]-1, y = [y] + lst{x} ], each [y] ) ), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMwMDIwUNJRMjQwMDYB0UDsnJOamAekjYDYWN/IQt/IwMhEKVYHi3KQEuf8xBIgZU5YtTEQByQmZ0PtwaLaEIdTjAkrR3KKBWHVSE4xQVUdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order #" = _t, Product = _t, Operation = _t, #"Operation name" = _t, #"Operation Leadtime" = _t, #"Order Plan date" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Product", Int64.Type}, {"Operation", Int64.Type}, {"Operation name", type text}, {"Operation Leadtime", type number}, {"Order Plan date", type date}}, "en-US"), // Added Aggregated Leadtime into inner [All] table GroupedRows = Table.Group(ChangedType, {"Order #"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {fn_AggLT(List.Buffer([Operation Leadtime]))}, Value.Type(_ & #table(type table[Aggregated Leadtime=number], {{}}))), type table}}), CombinedAll = Table.Combine(GroupedRows[All]), Ad_StartDate = Table.AddColumn(CombinedAll, "Start Date", each Date.AddDays([Order Plan date], -[Aggregated Leadtime]), type date) in Ad_StartDate
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMwMDIwUNJRMjQwMDYB0UDsnJOamAekjYDYWN/IQt/IwMhEKVYHi3KQEuf8xBIgZU5YtTEQByQmZ0PtQVIdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order #" = _t, Product = _t, Operation = _t, #"Operation name" = _t, #"Operation Leadtime" = _t, #"Order Plan date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Order #", Int64.Type},{"Product", Int64.Type},
{"Operation", Int64.Type}, {"Operation name", type text},
{"Operation Leadtime", Int64.Type}, {"Order Plan date", type date}
}),
//Assuming you will have more than one order number, process each ordder number separately
#"Grouped Rows" = Table.Group(#"Changed Type", {"Order #"}, {
{"Starts", (t)=>
let
//ensure operations in proper order
sorted = Table.Sort(t, {"Operation", Order.Ascending}),
//compute aggregate lead times
aggLeadTime = List.Accumulate(
{0..Table.RowCount(sorted)-1},
{},
(s,c)=> s & {List.Sum(List.RemoveFirstN(sorted[Operation Leadtime],c))}),
//compute start dates
startDt = List.Accumulate(
aggLeadTime,
{},
(s,c)=> s & {Date.AddDays(sorted[Order Plan date]{0},-c)}),
addColumns = Table.FromColumns(
Table.ToColumns(sorted)
& {aggLeadTime}
& {startDt},
Table.ColumnNames(sorted) & {"Aggregate Lead time"} & {"Start date"})
in
addColumns,
type table[#"Order #"=Int64.Type, Product=Int64.Type, Operation=Int64.Type, Operation name=text, Operation Leadtime=Int64.Type, Order Plan date=date, Aggregate Lead time=Int64.Type, Start date = date]
}}),
#"Expanded Starts" = Table.ExpandTableColumn(#"Grouped Rows", "Starts", {"Product", "Operation", "Operation name", "Operation Leadtime", "Order Plan date", "Aggregate Lead time", "Start date"})
in
#"Expanded Starts"
Thank you so much for your reply.
I realize that I perhaps should posted this in Desktop category and not in PowerQuerry because I'm not that advanzed in coding. Is it possible to solve my issue in DAX?
I also forgot to mention that data in my above table is coming from two different fact tables. I've merged two fact tables.
Hi Anonymous, why not to use Power Query solution? If you don't know how to use this query - read note at the botom of my post.
I've added 3 more rows to your sample data just to demonstrate that we consider every Order # separately.
EDIT: 2024-03-28 15:07 GMT+1: I've edited my code, and now it is realy fast also with big data
Result
let fn_AggLT = (lst as list)=> List.Reverse(List.Generate( ()=> [ x = List.Count(lst)-1, y = lst{x} ], each [x] >= 0, each [ x = [x]-1, y = [y] + lst{x} ], each [y] ) ), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMwMDIwUNJRMjQwMDYB0UDsnJOamAekjYDYWN/IQt/IwMhEKVYHi3KQEuf8xBIgZU5YtTEQByQmZ0PtwaLaEIdTjAkrR3KKBWHVSE4xQVUdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order #" = _t, Product = _t, Operation = _t, #"Operation name" = _t, #"Operation Leadtime" = _t, #"Order Plan date" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Product", Int64.Type}, {"Operation", Int64.Type}, {"Operation name", type text}, {"Operation Leadtime", type number}, {"Order Plan date", type date}}, "en-US"), // Added Aggregated Leadtime into inner [All] table GroupedRows = Table.Group(ChangedType, {"Order #"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {fn_AggLT(List.Buffer([Operation Leadtime]))}, Value.Type(_ & #table(type table[Aggregated Leadtime=number], {{}}))), type table}}), CombinedAll = Table.Combine(GroupedRows[All]), Ad_StartDate = Table.AddColumn(CombinedAll, "Start Date", each Date.AddDays([Order Plan date], -[Aggregated Leadtime]), type date) in Ad_StartDateHi,
Thanks allot. Is it possible for you to attach the sample as pbix file? It will be easier for me to solve it 🙂
It is, but it is not necessary 🙂 - Read note below my posts - there is a picture with explanation how to use my query. Don't forget to rename your columns - you provided different column names in sample data vs screenshot in next post.