Welcome to Excel Jet Consult blog post. In this Excel video tutorial, we will learn how to convert numbers to proper time format using Power Query. Let’s get started
Video
Sample Data
In the screenshot below, we have transaction data which is officially formatted as an Excel Table using CTRL + T and renamed as Data. Our goal is to convert the Request Time in column B from numbers to proper time format
Load Dataset To Power Query Editor
We need to get the formatted data into the Power Query editor. To do that:
- Select a single cell in the dataset
- In the Get & Transform Data group of the Data tab, click on From Table/Range
- Split Column By Number of Character
In order to achieve the correct result, we need to split the Request Time column into two columns. In total, we will have 3 columns each for hours, minutes and seconds respectively. To do that:
- Right-click on the Request Time column and Select Split Column
- In the Split Column, select By Number of Characters
In the Split Column dialogue box, we are going to split by using 2 as the number of characters
Very importantly, we need to specify the split type. By default, Power Query will split Repeatedly. But, that won’t give us what we wanted because we wants to split the seconds first, then we split the minutes.
- Therefore, select Once, as far right as possible split type
- Click OK
- Again, select the original Request Time column and click on Split Column
- Select By Number of Characters and select Once, as far right as possible split type
- Click OK
From the screenshot below, we have three columns. One housing splitted hours, minutes and seconds respectively
Merge Splitted Column using Separator
Next, we want to merge the splitted columns into one and then, we will provide the merge separator. To do this:
- Select all the three splitted columns only by clicking on the first one, hold down the shift key and click on the three column
- Right-click and select Merge Columns
- In the Separator dropdown of the Merged Columns dialogue box, select Colon
- Optionally provide meaningful for the new column
- Click OK
From the screenshot below, we can see that the coolumns have been merged into a single column with the colon separator.
Apply Time Data Type
To complete the process, we need to apply Time data type. To do that:
- Select the data type icon left of the heading and choose Time
- Click on Home tab and choose Close & Load To…
- In the import data wizard, Table is automatically selected. Therefore, Click OK
Voila! from the screenshot below, we now have a proper time format
See you in the next tutorial