Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Daily Operation Job Count Plan

Hi I am new to Power BI and this group, so apologies if this is a daft question but help would be appreciated, thank you. I have a set of data that shows daily orders booked in by operation. I also...
Need Help
jgeddes's avatar
jgeddes
Super User
3 years ago

Starting with a table,

You can get the resulting table,

with the follwing code...

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyNAASJkBsDsSWQGwMxGZgsVgdoBojINMUiI2gUsZQtglYPDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Operation Number" = _t, Capacity = _t, #"08/18/2023" = _t, #"08/19/2023" = _t, #"08/20/2023" = _t, #"08/21/2023" = _t, #"08/22/2023" = _t, #"08/23/2023" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Operation Number", "Capacity"}, "Date", "Order Count"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Operation Number"}, {{"Count", each _, type table [Operation Number=nullable text, Capacity=nullable number, Date=text, Order Count=number]}}),
Custom1 = Table.AddColumn(#"Grouped Rows", "IndexAdded", each Table.AddIndexColumn([Count],"Index",1, 1)),
#"Removed Columns" = Table.RemoveColumns(Custom1,{"Count"}),
#"Expanded Index" = Table.ExpandTableColumn(#"Removed Columns", "IndexAdded", {"Capacity", "Date", "Order Count", "Index"}, {"Capacity", "Date", "Order Count", "Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Index",{{"Operation Number", type text}, {"Capacity", Int64.Type}, {"Date", type date}, {"Order Count", Int64.Type}, {"Index", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Operation Number Order Sum", each List.Sum(Table.SelectRows(#"Changed Type", (x)=> x[Operation Number] = [Operation Number])[Order Count]), type number),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "maxIndex", each List.Max(Table.SelectRows(#"Added Custom", each Number.Mod([Operation Number Order Sum], [Capacity] * [Index]) <> [Operation Number Order Sum])[Index]), type number),
#"Added Custom1" = Table.AddColumn(#"Added Custom2", "optimizedOrderCount", each if [Index] <= [maxIndex] then [Capacity] else if [Index] = [maxIndex] + 1 then [Operation Number Order Sum] - ([maxIndex] * [Capacity]) else 0, type number),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Order Count", "Index", "Operation Number Order Sum", "maxIndex"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Date", type text}}, "en-US")[Date]), "Date", "optimizedOrderCount", List.Sum)
in
#"Pivoted Column"