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,
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.
well you made my day - thanks allot and happy eastern 🙂
So have you made it? You're welcome.
Thanks - It's working but not 100% and it's because I don't know how to use the script correctly yet.
Since Order number and plan date is in another table it will be tricky to use them. But it should be possible to aggregate the lead time based on product number and operation number, I will create "Start date" in DAX later.
Here's how I've modified your script (and yes I don't know how to exclude start date 🙂
The issue now is that it aggregate all lead time based on operation number, it doesn't take product number in account
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 = Sql.Database(Server, Database, [Query="SELECT
DimFacility_Key,
DimWorkCenter_Key,
DimItem_Key,
Facility,
[Product number],
[Product structure type],
[Operation number],
[Sequence number],
substring([Work center],1,3) as [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]FROM [MData].[FactOperations] WHERE [Product structure type] = 'PRO' AND Facility = '300' "]),
ChangedType = Table.TransformColumnTypes(Source,{{"DimFacility_Key", type text},
{"Facility", Int64.Type},
{"Product structure type", type text},
{"Sequence number", Int64.Type},
{"Work Center", Int64.Type},
{"Operation elements exists", Int64.Type},
{"Fixed time", Int64.Type},
{"Run time", Int64.Type},
{"Price and time quantity", Int64.Type},
{"Planned number of workers - setup", Int64.Type},
{"Planned number of workers - run time", Int64.Type},
{"Planned number of machine", Int64.Type},
{"Cumalative scrap percentage", Int64.Type},
{"Scrap percentage", Int64.Type},
{"Alternate operation", Int64.Type},
{"Production days", Int64.Type},
{"Product number", type number},
{"Operation number", Int64.Type},
{"Operation description", type text},
{"Lead time offset", type number}}, "en-US"),
// Added Aggregated Leadtime into inner [All] table
GroupedRows = Table.Group(ChangedType, {"Operation 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]),
Ad_StartDate = Table.AddColumn(CombinedAll, "Start Date", each [Aggregated Leadtime])
in
Ad_StartDateThis is how my original table looks like when I've merged with another table to get Order number and Planning date in the same table: