Hi,
I have a data set with event records with start and stop dates that span Month, Date and Time.
| 1/7/2016 | 8:00:00 AM | 1/7/2016 | 12:40:00 PM | Derate |
| 3/19/2016 | 8:06:00 PM | 3/21/2016 | 5:30:00 PM | Derate |
I'd like a record for each day with a start and end hour. My power query code works for the 3/19 event:
| 3/19/2016 20:06 | 3/20/2016 0:00 | Derate |
| 3/20/2016 0:00 | 3/21/2016 0:00 | Derate |
| 3/21/2016 0:00 | 3/21/2016 17:30 | Derate |
But my code is setting a same day event end hour to midnight:
| 1/7/2016 8:00 | 1/8/2016 0:00 | Derate |
The code is:
let
Source = Excel.CurrentWorkbook(){[Name="Table1_3"]}[Content],
Records = Table.ToRecords(Source),
DateTime.IsSameDay = (x, y) => Date.Day(x) = Date.Day(y) and Time.Hour(x) = Time.Hour(y),
Expand = (x) => List.Generate(
() => Record.Combine({x, [End=Date.EndOfDay(x[Start])]}),
(record) => record[Start] <= x[End],
(record) => let
NextStart = Date.StartOfDay(Date.AddDays(record[Start], 1)),
NextEnd = Date.EndOfDay(NextStart),
ThisEnd = List.Min({NextEnd, x[End]})
in
Record.Combine({record, [Start=NextStart, End=ThisEnd]})),
Transformed = List.Transform(Records, each if DateTime.IsSameDay([Start], [End]) then {_} else Expand(_)),
Combined = List.Combine(Transformed),
Result = Table.FromRecords(Combined),
#"Changed Type" = Table.TransformColumnTypes(Result,{{"Start", type datetime}, {"End", type datetime}, {"Activity", type text}})
in
#"Changed Type"
I truly appreciate your time and input on correcting the query code.
Thanks!
Hi Carolyn,
Please provide a sample Excel file with your data and query so we can see everything in context. Please also provide an example of the desired output. This can be in a separate Excel table that you mock up.
Thanks,
Mynda
Hi Mynda,
Here's a sample excel file. I'm working in excel 2010.
I just need to adjust the code to get the records that start and end on the same day to reflect the correct end time - not midnight.
For now the datasets are for one year files. I don't think I'll need to consider Year Month in the code.
Your help is always greatly appreciated!
PS - I adapted this code from a multiply rows based on Month Date concept that I found on the internet. Just can't get the Time piece working!
Hi Carolyn,
I'm confused, isn't your first query 'Table1_2' what you need? Your second query has 34 rows in it, but the first only has 23.
You originally said "I'd like a record for each day with a start and end hour." but that looks like what you have in query 'Table1_2'.
Do you want them all in a single column and the remove any duplicates?
I can see query 'Rec_Rows' is losing the time component of the date/time value, but I don't see what your desired output is and quey 'Rec_Rows' doesn't look right because it has more rows than the source data so I don't think there's much point in trying to fix this (at this stage).
What am I missing?
Mynda
Hi Mynda,
My apologies for not being clear and not using a better data sample. Upon revisiting my sample file, I realized that there was only one record that spanned more than one month.
Attached is V2.
The desired results are hours by month by activity using a date range selection. You will see on the Comparison tab that after I manually edited the Rec_Rows table end times, there are now .28 hours in October. This will not show up using the Query Table 1_2.
Thus, the Rec_Rows query works except it does not provide the correct end time for an activity that does not cross over into another day or month.
Thanks in advance for any assistance you can provide.
Hi Carolyn,
Try this:
Add a new query named AllMonths with this code:
MonthList = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"},
#"Converted to Table" = Table.FromList(MonthList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
#"Promoted Headers"
This will create an empty table with all months names.
Then, use this query to reformat data as you wanted:
FromDate=Excel.CurrentWorkbook(){[Name="Table9"]}[Content]{0}[#"From:"],
ToDate=Excel.CurrentWorkbook(){[Name="Table9"]}[Content]{0}[#"To:"],
Source = Excel.CurrentWorkbook(){[Name="Rec_Rows5"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each [Start] >= FromDate and [Start] <= ToDate),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Start", type datetime}, {"End", type datetime}, {"Activity", type text}, {"Hours", type number}, {"Month", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Activity", "Month"}, {{"Grouped data", each List.Sum([Hours]), type number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Month]), "Month", "Grouped data", List.Sum),
#"Appended Query" = Table.Combine({#"Pivoted Column", AllMonths}),
#"Reordered Columns" = Table.ReorderColumns(#"Appended Query",{"Activity", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"})
in
#"Reordered Columns"
Same code is in file attached. Note that I converted your date parameters to a table, to filter the data from query based on your inputs in H4:I4.
The results of the query are returned in Rec_Rows Date Range sheet, from G14.
Hi Catalin,
Thank you for your response. The code provided is very helpful in summarizing the results and I'll certainly use this in my file - and many others 🙂 .
However, my root problem is the end time for records that start and end on the same date. In my sample V2 file, I had to manually change the results of the Rec_Rows query in the Rec_Rows5 Table to get the correct end times for records that start and end on the same date. The start and end times for records that expand across more than one day are correct.
Any further assistance you can provide is greatly appreciated.
I see what you mean, the existing query that is splitting and generating extra rows is not working as expected, correct?
I'll look into that, will take few days, hope you're not in a rush.
Catalin
Hi Catalin,
I finally found my error!
I realized with splitting the records across dates that I only needed to compare Dates. Removed the Time.Hour in line 3
Original: DateTime.IsSameDay = (x, y) => Date.Day(x) = Date.Day(y) and Time.Hour(x) = Time.Hour(y),
Final: DateTime.IsSameDay = (x, y) => Date.Day(x) = Date.Day(y),
Also, by removing the second = symbol in line 6, I didn't wind up with 0 hour records for activities ending at midnight.
Original: (record) => record[Start] <= x[End],
Final: (record) => record[Start] < x[End],
Attached is the final sample. Thanks again for your help. I really like the concept of using the list function to create a date range summary!
Sounds great, glad to hear you managed to find the bug, this means that you have now a deeper understanding of what the code does, good job!
