• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Multiply rows based on date and time|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Multiply rows based on date and time|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryMultiply rows based on date and tim…
sp_PrintTopic sp_TopicIcon
Multiply rows based on date and time
Avatar
Carolyn Rainaud
Member
Members
Level 0
Forum Posts: 35
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
1
March 16, 2017 - 10:11 am
sp_Permalink sp_Print

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!

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4619
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 16, 2017 - 3:07 pm
sp_Permalink sp_Print

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

Avatar
Carolyn Rainaud
Member
Members
Level 0
Forum Posts: 35
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
3
March 17, 2017 - 2:06 am
sp_Permalink sp_Print

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!

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4619
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
March 17, 2017 - 11:37 am
sp_Permalink sp_Print

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

Avatar
Carolyn Rainaud
Member
Members
Level 0
Forum Posts: 35
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
5
March 19, 2017 - 1:28 am
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
March 20, 2017 - 6:12 pm
sp_Permalink sp_Print sp_EditHistory

Hi Carolyn,

Try this:

Add a new query named AllMonths with this code:

let
    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:

let
    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.

Avatar
Carolyn Rainaud
Member
Members
Level 0
Forum Posts: 35
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
7
March 30, 2017 - 11:11 pm
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
April 1, 2017 - 10:53 pm
sp_Permalink sp_Print

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

Avatar
Carolyn Rainaud
Member
Members
Level 0
Forum Posts: 35
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
9
April 2, 2017 - 12:02 am
sp_Permalink sp_Print

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!

sp_AnswersTopicAnswer
Answers Post
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1845
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
April 2, 2017 - 2:46 am
sp_Permalink sp_Print

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!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Alan Sidman, QSolutions Group
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
Hans Hallebeek: 185
Newest Members:
Kate Dyka
Kwaje Alfred Mogga
thong nguyen
Appiagyei Kofi Frimpong
Hilary Burchfield
Richie Wright
Adel Kock
Barbara Murray
Doug Milne
Siobhan Stringer
Forum Stats:
Groups: 3
Forums: 24
Topics: 6547
Posts: 28656

 

Member Stats:
Guest Posters: 49
Members: 32832
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.