I have an event data file in Excel. It has a start date, end date and start time, end time for all events. I created a column in Excel for Duration, which is End time - Start time.
When I bring this data into Power Query, the Duration column formats as DateTime. If I choose to format it as Duration, I am getting an error. I am attaching the Power BI file, the Excel data file,
and the screenshots.
My query is, how do I show the duration between the end time and start time for each event? Is there a way to do this within Power BI?
The Start date and End date columns must be set to the Date only data type.
I selected the Start Date column, held down the Control/Ctrl key and selected the Start time column then went to the Date option in the ribbon and selected combine date & time. I repeated this process with the End Date/End Time.
Converted these merged columns to Datetime format
I used this piece of M Code to get the duration between the two
= Table.AddColumn(#"Changed Type1", "ElapsedDuration", each Duration.ToRecord([#"Event End"] - [#"Event Start"]))
Which creates a record for each row as per below
I then selected the Expand Columns drop down to reveal Days, Hours, Minutes & Seconds as per below. I deselected Seconds and the Use original column name as prefix (that should always be deselected as default…anyway )
Clicked OK and it gave me a duration column for Days, Hours and Minutes as per below
Here is an updated one which transforms the days & hours into minutes adds them to the minutes column then creates a column which divides by 1440 (number of mins in a day) to give you an overall duration in day figure.
This is a bit scruffy but I best get back to my day job
I was trying to replicate the steps you described. However, after I select the Startdate to Start time columns, my Data menu on the ribbon is greyed out! Not sure what I am doing wrong. Sorry for the bother! Please let me know when time permits.
Ah, it’s my fault. I said to hold down shift…this selects the End date column also. Shift will select all the columns between 1st selected and last selected.
Control/Ctrl will only select the clicked on columns while held down.
So please use the Control/Ctrl button instead of the shift key
I will update the original response so no one else falls foul of my dodgy key memory
Yes, that worked. I played around with the format when it first didn’t work and that’s why it got the DateTime format.
I am new to M. Just know some basics. I copied and pasted this into MQuery and now getting an error. I am really sorry to be such a bother. My apologies!
I have created two queries, one where it stops after I created the Day, Hours & Min columns and another fuller solution where I take steps to give you a final Duration in Days column with un-needed columns removed.
M Code can be confusing, it’s taken me a while to get my head around it and I still get confused so don’t worry if you feel you don’t understand it yet. There is a lot to it. Start off with simple things first.
If you have access to the learning portal I highly recommend the Power Query/M Code courses by @Melissa. They’ve been a massive help to me (and many others).
Hopefully, how I’ve created these queries, make it clearer.
I just wanted to update you that I worked out creating the custom column and understood how to get the elapsed time using M. A big thank you, to you for helping me learn this.
)
