I have a logic that I am trying to implement using Power Query as this equally gives me an opportunity to leverage the power of M. I am grateful to the community for every help so far as I learn a lot from your contribution.
I am using Power Query to bucket hours into their respective months. I have a start date time and an end date time column from an EventLog table. When an event has ended, the closed date is formed otherwise the end date remains open if the event is still ongoing. But when reporting on this I use the current time as an end date to close the event. This is where the logic begins.
When an event runs over multiple months, I split the duration (in hours) into multiple months. The first and last month of an event may be decimals but in between it should be something like 720.00, 744.00 etc, depending on the month because for a month with 31 days you should have 744.00.
Currently, from the image above, what I am getting is not accurate. My logic in Power Query needs improving.
My query:
let
Source = Excel.Workbook(File.Contents("C:\Users\saint\Downloads\Event_2.xlsx"), null, true),
Event_2_Sheet = Source{[Item="Event_2",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Event_2_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"EventStartDateTime", type datetime}, {"EventEndDateTime", type datetime}}),
#"Replaced null" = Table.ReplaceValue(#"Changed Type", null, DateTime.LocalNow(), Replacer.ReplaceValue, {"EventEndDateTime"}),
// essential step to replicate the logic of the recursive cte
n_Interval = Table.AddColumn(
#"Replaced null",
"n_Month",
each let
diff_yr = Date.Year([EventEndDateTime])-Date.Year([EventStartDateTime]),
diff_m = Date.Month([EventEndDateTime])-Date.Month([EventStartDateTime]),
n = diff_yr*12+diff_m, l = List.Accumulate({1..n},{[EventStartDateTime]},
(s,c) => s & {Date.StartOfMonth(Date.AddMonths([EventStartDateTime],c))}),
cols = {l, List.Skip(l)&{[EventEndDateTime]}}
in Table.FromColumns(cols, {"Year","Month"})
),
#"Expanded n_Month" = Table.ExpandTableColumn(n_Interval, "n_Month", {"Year","Month"}),
TotalHours = Table.AddColumn(#"Expanded n_Month", "Hours", each Number.Round(Duration.TotalHours([Month]-[Year]),2)),
#"Transformed Cols" = Table.TransformColumns(TotalHours, {{"Year", each DateTime.ToText(_, "yyyy")}, {"Month", each DateTime.ToText(_, "MMMM")}}),
#"Removed Columns" = Table.RemoveColumns(#"Transformed Cols",{"EventStartDateTime", "EventEndDateTime"})
in
#"Removed Columns"
I did this in SQL Server code here and it works well
I have asked similar questions before but I am required to come up with lots of alternatives for performance’s sake.
I expect to get a result like the below, a correction from the first image above
My files are attached
Eventlog.pbix (90.8 KB)
Event_2.xlsx (17.0 KB)
I sincerely appreciate your effort