Bottom line: Learn how to use Power Query to convert times stored as text [## hours ## minutes ## seconds] to time values [h:mm:ss] that can be used for calculations and data analysis in Excel.
Skill level: Intermediate
The Data Cleansing Challenge
This post is the second in a series on solutions to the data cleansing challenge I presented in a previous post. The challenge is to use any tools in Excel to convert the time/duration stored a text into a numeric value that can be formatted as a time.
The original data set contains a column of time/duration text that are in the following format.
1 hour 27 minutes 15 seconds
We need to convert it to a number in Excel with a time format of h:mm:ss that looks like the following.
1:27:15
Power Query Solutions
There were a ton of awesome solutions submitted on the original post and YouTube video. Thanks again if you submitted a solution!
In this post & video we look at a solutions using Power Query. There were quite a few different Power Query submissions. Thanks to Walt, Renato, Kirk, XLarium, Martin, and everyone else for participating!
In this post and video we look at the solution from Walt. I like this solution because it just uses the buttons/tools in the Power Query Editor ribbon, and does not require us to write custom formulas with M-code.
I believe it's a good demonstration of how easy and useful Power Query can be.
Download the Excel File
Download the example Excel file to follow along.
The file uses Excel Tables and Power Query. Here are posts to help get you started with these tools:
- Power Query: An Introduction to Excel's Most Powerful Data Tool
- The Complete Guide to Installing Power Query
- A Beginner's Guide to Excel Tables
Video Tutorial
In the video below I walk through how to solve the challenge Power Query. The process is similar to the formula based solution with the text functions. We separate the times based on each component (hour, minute, second), then merge the columns and change the data type.
Explanation of the Query Steps
Here is a list of the applied steps in the query:
1. Lowercase the Time column
Power Query Action: Select Time column > Transform tab > Format button > lowercase
*Also available on column right-click menu
Purpose: Converts all text in the column to lowercase. This step is not necessary for this data set. However, Power Query (M Language) is case sensitive and it's a good idea to convert the case if you are not familiar with the data set, and will be using functions that search the text for patterns.
2. Trim text in the Time column
Power Query Action: Select Time column > Transform tab > Format button > Trim
*Also available on column right-click menu
Purpose: Trim any leading or trailing blank spaces from the text. This step is also not necessary for this data set, but a good data cleansing practice/habit since the text length is important in the extraction steps.
3. Extract Hours
Power Query Action: Select Time column > Add Column tab > Extract > Text Before Delimiter
Delimiter: hour (<there is a space before hour)
Advanced options:
Scan for the delimiter: From the end of the input
Purpose: Add a new column for the numeric values for hours. Find the phrase ” hour” and return any text before it. Using “From the end of the input” excludes rows that do not contain the delimiter because the search is done from right-to-left and does not find the delimiter.
This also handles the issue of the delimiter being both singular (hour) or plural (hours).
4. Extract Minutes
Power Query Action: Select Time column > Add Column tab > Extract > Text Between Delimiters
Start delimiter: min (<there is a space before min)
End delimiter: (<this is a single space)
Advanced options:
Scan for the start delimiter: From the end of the input
Scan for the end delimiter: From the start delimiter, toward the start of the input
Purpose: Add a new column for the numeric values for minutes. Find the phrase ” min” and return any text before it and after the preceding space character. Using “From the end of the input” searches right-to-left.
Using “From the start delimiter, toward the start of the input” excludes the delimiter and everything after. This is useful because the delimiter can be both singular (minute) or plural (minutes). Both cases are handled with these settings.
5. Extract Seconds
Repeat step 4 and change the start delimiter to: sec
(<there is a space before sec)
Purpose: Add a new column for the numeric values for seconds.
6. Replace Blanks in the new time columns
Power Query Action: Select 3 new time value columns > Transform tab > Replace Values button
Value to find: (leave field blank)
Replace with: 0
*Also available on column right-click menu
Purpose: Replace the blank cells in the new time columns with zeros. This is required for place holders in the next merge step.
7. Merge the time columns
Power Query Action: Select 3 new time value columns > Transform tab > Merge Columns
Separator: Colon
New column name: Time Value
*Also available on column right-click menu
Purpose: Create a text string that Power Query can recognize and convert to a time or duration data type.
8. Change data type to Duration
Power Query Action: Select Time Value column > Transform tab > Data Type > Duration
*Also available on column right-click menu
Purpose: Convert the text in the column to a time or duration data type. Using Duration will change the format in the output table in Excel to d.h:mm:ss. This saves us a step in Excel.
What if the duration greater than 24 hours?
A few questions came up about the time being more than 24 hours. In this case we can still use Power Query, but need to modify the steps.
I included another query in the sample file “Text to Time – >24 Hrs” that handles this scenario. We can use the same principle we saw in the last post with SUMPRODUCT to multiple the time columns by the number of seconds in each component.
Here is the formula for the Custom Column.
(([Hours]*3600)+([Minutes]*60)+[Secs])/86400
There are 3,600 seconds in an hour, 60 seconds in a minute, and 86,400 seconds in a day. The result is a decimal number that can be converted to a Duration data type.
Checkout my post on the date system in Excel to learn more about this conversion from decimal number to a time value.
Conclusion
Power Query is an awesome data automation tool that is great for this type of data cleansing. The advantage of using this technique is that we only have to do the setup work once. When we get new data, we can simply refresh the query.
If the data is exported from the system to a CSV or Excel file, then we can setup the query to import the data from one or all files in a folder. Meaning you just have to download the file, move it to a folder, and then refresh the query. Easy as 1, 2, 3. 😉
Checkout my articles on an Overview of Power Query and How to Install Power Query for more details.
Please leave a comment below with any questions or suggestions. Thank you! 🙂
Just yesterday I thought of the following solution :
– under data I used the “text to colums” this gives me 2 to 6 colums with the numbers in separate columns
– then I used the replace command to replace hour(s) by 3600, minute(s) by 60 and second(s) by 1
– then I multiplied the colums 2 by 2 and added them giving me the total seconds
– dividing this result by (24*60*60) gives me the result that I can format as time
Waiting for your comments.
Best regards
Paul
Paul
Hi Paul, I love it! This is a great solutions. It’s a few more steps with replacing the singular and plural time components, but pretty easy to implement.
It looks like some people don’t have the buttons for Text Between Delimiters yet, and this would be another good workaround. I’ll do a follow-up video on this in the future.
Thanks so much for sharing!
Well presented, unfortunately its the frist video i was lost at around 3 minutes, I am using an newer excel and in the Add Colum tab there is no Extract button in the From Test area, only a Length option but that doesnt help, any plans to show this solution in Excel 2016?
Hi Mark,
I’m sorry to hear that. It sounds like you might be using the stand-alone version of Office 2016. That version does not receive updates to Power Query. In the video I’m using Office 365, which does include updates. I believe the menu items for Text Before/After/Between Delimiter were added in 2017 to Power Query in Office 365. I believe that users on Excel 2010 or 2013 that use the Power Query add-in would also have the update. I’ll see if I can confirm that.
You can also create these steps with Custom Columns and the Text.BeforeDelimiter and Text.BetweenDelimiters functions. Here are the formulas for each step.
Hour: =Text.BeforeDelimiter([Time], ” hour”, {0, RelativePosition.FromEnd})
Min: =Text.BetweenDelimiters([Time], ” minute”, ” “, {0, RelativePosition.FromEnd}, {0, RelativePosition.FromEnd})
Sec: Text.BetweenDelimiters([Time], ” second”, ” “, {0, RelativePosition.FromEnd}, {0, RelativePosition.FromEnd})
The buttons in the ribbon obviously make it much easier than writing those formulas.
There is another query from Renato in the sample workbook that uses a different approach with If statements in a custom column. This will require writing formulas, but should work for you.
I hope that helps. Thanks again for posting this reply. It’s difficult to track all versions and features, but I’ll try my best to note that for future videos.
I get an error in the Renato solution.
The error I see in the query is at the line ‘SplitColumnbyCharacterTransition’.
The error I get is ‘Expression.Error: The name Splitter.SplitTextByCharacterTransition wasn’t recognized.’.
Looking forward to your comments.
Best regards,
Jo
Hi Jo,
I’m sorry to hear that. I’m guessing you are on an older version of Power Query that does not have that function yet. If you are using the stand-alone version of Excel 2016, and NOT Office 365, then Power Query will not receive updates.
Here is a picture of the menu item that Renato used.
Paul posted another solution in the comments section of this page that should work for any version. I’ll do a followup video on that in the future.
I hope that helps. Thanks again and have a nice weekend! 🙂