Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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"
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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
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
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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"
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.