Forum Discussion
Aggregate in numerical order
Hi - I will be glad if you can help me to solve following: My current table consist of manufacturing Order which includes 3 operations (sometimes more) and a final plan date for the last operatio...
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
Hi, in future upload your data to google drive for example, because I had to make some transformations to make it usable 😉
I'm missing any date column in this new sample, so I'm not able to calculate [Start Date] as you requested in 1st post.
You can find added [Aggregated Leadtime] in this query. You have to add date column and then add calculated column:
each Date.AddDays(Date.From([Your_Date_Column]), -Int64.From([Aggregated Leadtime]))
One more importand note: Data is grouped by [Sequence Number] column. If you want to group by different column, change it in GroupedRows step.
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("pZRNDsIgEIXv0jWLYSjQrj2AxrhrXDTGGKPRhS49vFNAM6WjopKUn4EpXx4Puq5yNwNQKWp1A48egAFrLQ3iJAsslnOqcYi2VIbJZhjMjtv+RG1YzlrL+s/4kOaUxzBeq18p6inFub+mTaAEwqraCRCm0cUQjkFQHtWr7eVaTZWIvxQxQNWSFoCuGKNhGOgSBWY6mByJQ6DC9k8t4sqRGIt+c9ifdsKhvNFDK4lEey6HozIiSQHBoN69MWgtQGD6coIWsJiA+4Lypr7Q2aY5AJlC9IQtRhh5wmaeKEPQHx3hqYwQUmB6DtEQr85Bi26wwZjSHQVTTsEfChMgvngnQlD5iLC+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DimFacility_Key = _t, DimWorkCenter_Key = _t, DimItem_Key = _t, Facility = _t, #"Product number" = _t, #"Product structure type" = _t, #"Operation number" = _t, #"Sequence number" = _t, WorkCenter = _t, #"Operation description" = _t, #"Alternate operation" = _t, #"Operation elements exists" = _t, #"Fixed time" = _t, #"Run time" = _t, #"Price and time quantity" = _t, #"Planned number of workers - setup" = _t, #"Planned number of workers - run time" = _t, #"Planned number of machine" = _t, #"Scrap percentage" = _t, #"Cumalative scrap percentage" = _t, #"Lead time offset" = _t, #"Production days" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Lead time offset", type number}}),
// Added Aggregated Leadtime into inner [All] table
GroupedRows = Table.Group(ChangedType, {"Sequence number"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {fn_AggLT(List.Buffer([Lead time offset]))}, Value.Type(_ & #table(type table[Aggregated Leadtime=number], {{}}))), type table}}),
CombinedAll = Table.Combine(GroupedRows[All])
in
CombinedAll