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
Hi, I don't see [Operation Leadtime] in your table which is necessary for this purpose. Could you provide maybe 10-15 rows of your real data (whole table) either as text or upload it to google drive for example. Replace sensitive data with some dummy values.
Thanks.
Operation leadtime is "lead time offset".
Here's my tabel with dummy numbers: (I tried several time to insert this as table but keep getting error message about invalid HTML)
DimFacility_Key DimWorkCenter_Key DimItem_Key Facility Product number Product structure type Operation number Sequence number Work Center Operation description Alternate operation Operation elements exists Fixed time Run time Price and time quantity Planned number of workers - setup Planned number of workers - run time Planned number of machine Scrap percentage Cumalative scrap percentage Lead time offset Production days
6|300 6|18000 6|10030555 300 10030555 PRO 20 9999 180 Clean 0 1 0 1 5 0 1 1 0 19 6,72 0
6|300 6|18000 6|10030555 300 10030555 PRO 40 9999 180 Coat 0 0 0 1 5 0 1 1 0 19 5,46 0
6|300 6|38100 6|10030555 300 10030555 PRO 60 9999 381 Test 0 1 0 1 100 0 1 1 0 19 0,42 0
6|300 6|02600 6|10030555 300 10030555 PRO 80 9999 26 Test2 0 0 1 3 1 0 1 1 0 19 2,29 0
6|300 6|38100 6|10030555 300 10030555 PRO 100 9999 381 Packing 0 0 0 1 100 0 1 1 0 19 1,9 0
6|300 6|17600 6|10036666 300 10036666 PRO 20 9999 176 Clean 0 1 0 1 4 0 1 1 0 2 2 0
6|300 6|90200 6|10036666 300 10036666 PRO 60 9999 902 Test 0 1 1 1 1 0 1 1 0 2 0,4 0
6|300 6|02500 6|10036666 300 10036666 PRO 80 9999 25 Test2 0 1 1 1 1 0 1 1 0 2 0,1 0
6|300 6|38100 6|10037777 300 10037777 PRO 20 9999 381 Clean 0 1 0 1 10 0 1 1 0 15 2,6 0
6|300 6|03100 6|10037777 300 10037777 PRO 40 9999 31 Coat 0 0 0 1 5 0 1 1 0 15 0,7 0
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 CombinedAllThank you soooo much. It's fixed now. I have both aggregated leadtime and start date (did it in DAX):
I only had to do one minor change;
Table.Group(ChangedType, {"Sequence number"},
to
Table.Group(ChangedType, {"Product number"},
That's great 😉 You're welcome.