How to disaggregate production order in hourly buckets

[saad_mce]

Hi all,

I have got a complex problem to solve.

I have got below production orders starting in one month and ending in another month. I need to separate out the production requirements between months. So I have to disaggregate them by hours preferably. 

Resource	Material	Start DateTime	Finish DateTime	Duration (hours)	Order Qty	Output/hr
Line1	11851	30/11/2021 23:30:00	01/12/2021 04:00:00	4.50	12,350	2,744
Line1	11852	01/12/2021 04:45:00	01/12/2021 07:30:00	2.75	8000	2,909

I need the below table as output:

Resource	Material	Start DateTime	Finish DateTime	Output/hr
Line1	11851	30/11/2021 23:30	1/12/2021 0:00	1,372
Line1	11851	1/12/2021 0:00	1/12/2021 1:00	2,744
Line1	11851	1/12/2021 1:00	1/12/2021 2:00	2,744
Line1	11851	1/12/2021 2:00	1/12/2021 3:00	2,744
Line1	11851	1/12/2021 3:00	1/12/2021 4:00	2,744
Line1	11852	1/12/2021 4:45	1/12/2021 5:00	727
Line1	11852	1/12/2021 5:00	1/12/2021 6:00	2,909
Line1	11852	1/12/2021 6:00	1/12/2021 7:00	2,909
Line1	11852	1/12/2021 7:00	1/12/2021 8:00	1,455

Can we do this in power query and also in dax. I need to solve this in both methods. I can then judge which method is better suited to my data model and file.

Any support and help is appreciated.

Regards,

Abrar 

[KNP · Accepted solution]

Ok, I think I understand what you're after.

I have a similar process which I have briefly adapted to your scenario so it won't be perfect yet.

Paste the below into a blank query in the advanced editor and examine the steps.

Come back to me with any questions you have.

As far as DAX goes. I don't believe DAX is the right place to do this type of data modelling. Others may disagree.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ZY0xDoAwDAO/gjJXrZ0mKvAGflB1ZGDh/yOFSkiI6eKTYtcq23HulCDk7DczEpkUyknzmrEC3YKJOiysq2Et+g1qyM+hxUxa+Jbq7938X1reKY3FO2aMtGCR1i4=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Resource = _t,
        Material = _t,
        StartDateTime = _t,
        FinishDateTime = _t,
        DurationHours = _t,
        OrderQty = _t,
        OutputHour = _t
      ]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"Resource", type text},
      {"Material", Int64.Type},
      {"StartDateTime", type datetime},
      {"FinishDateTime", type datetime},
      {"DurationHours", type number},
      {"OrderQty", Int64.Type},
      {"OutputHour", Int64.Type}
    }
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "QtyHour",
    each [OrderQty] / (Duration.TotalDays([FinishDateTime] - [StartDateTime]) * 24)
  ),
  #"Duplicated Column" = Table.DuplicateColumn(
    #"Added Custom",
    "StartDateTime",
    "StartDate"
  ),
  #"Duplicated Column1" = Table.DuplicateColumn(
    #"Duplicated Column",
    "FinishDateTime",
    "FinishDate"
  ),
  #"Changed Type2" = Table.TransformColumnTypes(
    #"Duplicated Column1",
    {
      {"StartDate", type date},
      {"FinishDate", type date}
    }
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Changed Type2",
    "DaysBetween",
    each {Number.From([StartDate]) .. Number.From([FinishDate])}
  ),
  #"Expanded DaysBetween" = Table.ExpandListColumn(#"Added Custom1", "DaysBetween"),
  #"Changed Type3" = Table.TransformColumnTypes(
    #"Expanded DaysBetween",
    {{"DaysBetween", type datetime}}
  ),
  #"Added Custom3" = Table.AddColumn(
    #"Changed Type3",
    "HoursDay",
    each
      if DateTime.Date([DaysBetween])
        = [StartDate] and DateTime.Date([DaysBetween])
        = [FinishDate]
      then
        Duration.TotalDays([FinishDateTime] - [StartDateTime]) * 24
      else if DateTime.Date([DaysBetween]) = [FinishDate] then
        Duration.TotalDays([FinishDateTime] - [DaysBetween]) * 24
      else if DateTime.Date([DaysBetween]) = [StartDate] then
        Duration.TotalDays(Date.AddDays([DaysBetween], 1) - [StartDateTime]) * 24
      else
        24
  ),
  #"Added Custom2" = Table.AddColumn(
    #"Added Custom3",
    "QtyDay",
    each [HoursDay] * [QtyHour]
  ),
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Added Custom2",
    {{"QtyDay", type number}}
  )
in
  #"Changed Type1"

[saad_mce]

Hi KNP,

Apologies for late reply. I was away for a few days. 

This works great. Thanks for the codes.

I only have two small problem.

1. Our factory is closed on Sat & Sunday. How do we avoid the calculation being perfomed on Sat & Sunday? 

2. Our factory starts on Sunday 11PM. Is there a way to consider a factory calendar in these calculation by a supporting query/table?

Regards,

Abrar

[KNP]

Hi Abrar,

No problem.

Off the top of my head, I'd be looking to use the Date.DayOfWeekName() or Date.DayOfWeek() to work out if the start or end date included the weekend and then stopping/starting the calculation at the appropriate time.

It may actually be easier than that. You may be able to do the day calculation after the #"Added Custom3" step and then subtract 24 from all Saturdays and 23 from all Sundays.

If you finish earlier than midnight on Friday you'd need to take this into account also.

I don't have any time to spend on this now so hopefully this makes sense and gets you closer to a complete solution. I'll have a look at it later in the week if I get a chance.

Regards,

Kim

[KNP]

Actually, I think this will get you a little closer...

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ZY4xDoQwDAS/glxHZNd2LsAb+AFKSXHN/b+8hCAklGrtkTze45D9+zspQcgltTREMiqUk9pm2IBKwUjtFF5Rpz6nFtRg16DZXUp4S3U49zRK8/NK55xqLOjbinVQWuv53NJu3efdsaImuEVSyh8=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Resource = _t,
        Material = _t,
        StartDateTime = _t,
        FinishDateTime = _t,
        DurationHours = _t,
        OrderQty = _t,
        OutputHour = _t
      ]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"Resource", type text},
      {"Material", Int64.Type},
      {"StartDateTime", type datetime},
      {"FinishDateTime", type datetime},
      {"DurationHours", type number},
      {"OrderQty", Int64.Type},
      {"OutputHour", Int64.Type}
    }
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "QtyHour",
    each [OrderQty] / (Duration.TotalDays([FinishDateTime] - [StartDateTime]) * 24)
  ),
  #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "StartDateTime", "StartDate"),
  #"Duplicated Column1" = Table.DuplicateColumn(
    #"Duplicated Column",
    "FinishDateTime",
    "FinishDate"
  ),
  #"Changed Type2" = Table.TransformColumnTypes(
    #"Duplicated Column1",
    {{"StartDate", type date}, {"FinishDate", type date}}
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Changed Type2",
    "DaysBetween",
    each {Number.From([StartDate]) .. Number.From([FinishDate])}
  ),
  #"Expanded DaysBetween" = Table.ExpandListColumn(#"Added Custom1", "DaysBetween"),
  #"Changed Type3" = Table.TransformColumnTypes(
    #"Expanded DaysBetween",
    {{"DaysBetween", type datetime}}
  ),
  #"Added Custom3" = Table.AddColumn(
    #"Changed Type3",
    "HoursDay",
    each
      if DateTime.Date([DaysBetween]) = [StartDate] and DateTime.Date([DaysBetween]) = [FinishDate] then
        Duration.TotalDays([FinishDateTime] - [StartDateTime]) * 24
      else if DateTime.Date([DaysBetween]) = [FinishDate] then
        Duration.TotalDays([FinishDateTime] - [DaysBetween]) * 24
      else if DateTime.Date([DaysBetween]) = [StartDate] then
        Duration.TotalDays(Date.AddDays([DaysBetween], 1) - [StartDateTime]) * 24
      else
        24
  ),
  #"Inserted Day Name" = Table.AddColumn(
    #"Added Custom3",
    "Day Name",
    each Date.DayOfWeekName([DaysBetween]),
    type text
  ),
  #"Added Custom2" = Table.AddColumn(
    #"Inserted Day Name",
    "QtyDay",
    each
      if [Day Name] = "Saturday" then
        0 * [QtyHour]
      else if [Day Name] = "Sunday" and [HoursDay] > 1 then
        1 * [QtyHour]
      else
        [HoursDay] * [QtyHour]
  ),
  #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2", {{"QtyDay", type number}})
in
  #"Changed Type1"