Split the Time Using Power Query or Get & Transform

Managing time data in Excel can be straightforward, but when you need to perform deeper analysis—like calculating total hours, tracking service times, or aggregating time-based events—having your data split into hours, minutes, and seconds can make your life much easier. Fortunately, Excel’s Power Query or Get & Transform feature (available natively in Excel 2016 and via add-ins in 2010 and 2013) lets you do this cleanly, efficiently, and without complex formulas. In this article, we’ll walk you step by step through splitting a time column into hours, minutes, and seconds using Power Query.

Key Takeaways:

• Power Query (or Get & Transform) efficiently splits time into hours, minutes, and seconds without formulas.
• Converting your dataset into an Excel Table is essential before using Power Query.
• Automated transformations save time by applying the same steps to new data with just a refresh.
• Splitting time allows easier analysis, filtering, and aggregation for reporting purposes.
• Power Query handles large datasets faster and cleaner than Excel formulas like HOUR(), MINUTE(), and SECOND().

 

Why Use Power Query to Split Time?

Before diving into the steps, let’s understand why Power Query is the ideal tool for this task:

• Automated Transformations: Once you set up your query, any future updates to the dataset automatically apply the same transformation. You don’t need to repeat formulas for new rows.
• Cleaner Data Processing: Power Query allows you to transform data in a separate editor without altering your original dataset.
• Avoid Complex Formulas: Splitting time using Excel formulas like HOUR(), MINUTE(), and SECOND() works, but with large datasets, formulas can slow down your workbook. Power Query handles large volumes of data efficiently.
• Integrated with Excel Tables: Power Query works seamlessly with Excel tables, meaning your structured data is always ready for analysis or reporting.

 

Step-by-Step Guide: Split the Time Using Power Query

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 

Excel 2013 & 2010:

STEP 3: This will open up the Power Query Editor. Let us now get the Hour, Minute and Second.

Make sure the Order Time column is selected. Go to Add Column > From Date & Time > Time> Hour > Hour

Make sure the Order Time column is selected. Go to Add Column > From Date & Time > Time> Minute

Make sure the Order Time column is selected. Go to Add Column > From Date & Time > Time> Second

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated records!

You now have your times split to Hour, Minute, and Second!

 

Tips for Working with Split Time Data

Splitting time into separate components opens up many possibilities for analysis. Here are a few tips:

• Aggregations: Use the Hour, Minute, and Second columns to calculate totals or averages. For instance, you could find peak hours for orders.
• Filtering: Easily filter for specific hours of the day without writing formulas.
• Custom Calculations: Combine these columns into new calculations, like total seconds (Hour*3600 + Minute*60 + Second) for precise time-based metrics.
• Automation: Any new data added to your Excel table can be refreshed in Power Query, automatically applying the same transformation.

 

FAQs

1. What is the difference between Power Query and Excel formulas for splitting time?

Power Query splits time by adding new columns for hour, minute, and second in a separate editor, while formulas like HOUR(), MINUTE(), and SECOND() work directly in the worksheet. Formulas can slow down large datasets, whereas Power Query handles them efficiently. Power Query also allows easy automation, so the same transformations apply to new data automatically. Formulas need to be copied or dragged for each new row. Overall, Power Query is more scalable and easier to maintain for recurring tasks.

2. Can I split time from a dataset that contains both date and time?

Yes, Power Query can handle datetime columns. You can extract both the date and time components separately. Then, you can further split the time into hours, minutes, and seconds. This is useful for trend analysis or reporting by day and hour. The transformations are reusable and update automatically when new data is added.

3. Do I need Power Query installed separately for Excel 2010 and 2013?

Yes, Power Query is an add-in for Excel 2010 and 2013. You must download and install it from Microsoft’s official site. For Excel 2016 and later, it is integrated as Get & Transform, so no separate installation is required. Once installed, the steps to split time are essentially the same across versions. The interface might look slightly different but the functionality remains consistent.

4. How do I refresh the split time data when new records are added?

After adding new records to your Excel Table, go to the Data tab and click Refresh All. Power Query automatically applies the same transformation steps to the new data. This eliminates the need to redo formulas or manually split time again. The transformed data appears in the output table or worksheet you set when loading. Refreshing ensures your analysis is always up to date.

5. Can I perform calculations using the split time columns?

Absolutely. Once you have Hour, Minute, and Second columns, you can calculate totals, averages, or convert the time into seconds for precise analysis. For example, Total Seconds = Hour*3600 + Minute*60 + Second. You can also filter by specific hours, compute peak times, or prepare data for dashboards and reports. Power Query keeps the transformations dynamic, so any calculations based on the split columns automatically update when new data is refreshed.