How to Separate Date and Time in Power BI

Working with dates and times in Power BI can sometimes feel tricky, especially when your source data puts them both into a single column. Separating them is a common task that makes your analysis cleaner and more powerful. This tutorial will walk you through the easiest ways to split a datetime column into separate Date and Time columns using both Power Query and DAX.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Separate Date and Time in Your Reports?

Before jumping into the "how," it's helpful to understand the "why." A combined datetime column (like an order timestamp or event log) is great for precision, but it can be clunky for analysis. Here’s why splitting them is a good idea:

• Better Analysis by Date: If you want to analyze sales trends by day, week, or month, the time component just adds noise. Separating the date allows you to easily group transactions by the calendar date they occurred on.
• Analysis by Time of Day: By isolating the time, you can answer questions like, "What are our peak business hours?" or "When do most customer support chats come in?" This is impossible when time and date are locked together.
• Improved Visualization: Charting data on an axis with a full datetime value can create a messy, unreadable visual. Using just the date or just the time for your charts makes them far easier for stakeholders to understand.
• Simplified Relationships: Separating the date is a critical first step if you want to use a Date Table (a business intelligence best practice). A Date Table lets you perform advanced time-intelligence calculations, and you can only connect it to a clean date column, not a datetime column.

Two Core Methods: Power Query vs. DAX

In Power BI, there are two primary environments where you can transform data: the Power Query Editor and the DAX formula engine. Both can be used to separate date and time, but they serve different purposes.

1. The Power Query Method (Recommended)

Power Query is the data transformation engine in Power BI. You use it to clean, shape, and prepare your data before it gets loaded into your data model. Separating dates and times here is generally considered the best practice.

• When to use it: Use Power Query when you're setting up your data for the first time. The changes you make are applied to the data as it's loaded, resulting in a cleaner, more efficient data model. It's the "set it and forget it" approach.

2. The DAX Method

DAX (Data Analysis Expressions) is the formula language used in Power BI to create calculations like measures and calculated columns after your data is already in the model. You can use it to add new columns based on existing ones.

• When to use it: Use DAX if you are unable to edit the source query or need to add a calculation on the fly without going back into the Power Query Editor. It’s flexible but adds columns on top of your existing data model rather than cleaning the source.

For most scenarios, we recommend using Power Query. It keeps your base tables clean and improves report performance. Let's start there.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 1: How to Separate Date and Time in Power Query

This is the most straightforward method and ideal if you're building a new report. Follow these steps to split your datetime field using Power Query’s user-friendly interface.

Step 1: Open the Power Query Editor

From the main Power BI Desktop window, find the Home tab and click on Transform data. This will launch the Power Query Editor in a new window, where you'll see a preview of your data tables.

Step 2: Select Your DateTime Column

In the Power Query Editor, find and click on the header of the column that contains the combined date and time data. In our example, we'll use a column named "Order DateTime." Once selected, the column will be highlighted.

Step 3: Add a New Column for "Date Only"

With the datetime column selected, go to the Add Column tab in the ribbon at the top of the window. In the "From Date & Time" section, click the Date button and then select Date Only from the dropdown menu.

Power Query will instantly create a new column named "Date" containing only the date portion from your original column.

Step 4: Add a New Column for "Time Only"

Reselect the original "Order DateTime" column. Navigate back to the Add Column tab. This time, click the Time button and choose Time Only from the dropdown.

A new column named "Time" will appear containing just the time portion of the data.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 5: Clean Up and Close

Now you have three columns: the original datetime column, a Date column, and a Time column. You can clean this up:

• Rename Columns: Double-click the headers of your new columns to give them more specific names, like "Order Date" and "Order Time."
• Check Data Types: Power Query is usually smart about this, but ensure your "Order Date" column has a Date data type and "Order Time" has a Time data type. You can check this by looking at the icon next to the column name (a calendar for date, a clock for time).
• Remove Original Column (Optional): If you no longer need the combined datetime column, right-click its header and select Remove. This keeps your model lean.

Once you’re finished, click Close & Apply on the Home tab to save your changes and load the data into your Power BI model.

Method 2: How to Separate Date and Time with DAX Formulas

If you prefer or need to work within the main Power BI environment, you can achieve the same result by creating calculated columns using DAX. This method adds new columns to your table without modifying the source query.

Step 1: Navigate to the Data View

In Power BI Desktop, click on the Data icon in the left-hand navigation pane to view your tables.

Step 2: Create a New Column for the Date

Make sure your desired table is selected in the "Data" pane on the right. In the ribbon, click on the New column button.

This will open up the formula bar. To extract the date, you can use a simple DAX formula. We recommend using the following because it's clean and preserves the correct data type:

Order Date = 'Sales'[Order DateTime].[Date]

Alternatively, the INT() function also works perfectly. Dates and times are stored as numbers in Power BI, where the whole number represents the date and the decimal represents the time. INT() simply removes the decimal portion.

Order Date = INT('Sales'[Order DateTime])

Press Enter. A new column named "Order Date" will appear. Power BI may format it as a datetime initially. To fix this, select the new column and, in the Column tools tab, change the Data type to Date.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Create a New Column for the Time

Next, click New column again to create the time column. In the formula bar, enter this DAX formula:

Order Time = TIME( HOUR('Sales'[Order DateTime]), MINUTE('Sales'[Order DateTime]), SECOND('Sales'[Order DateTime]) )

This formula explicitly constructs a time value by extracting the hour, minute, and second from the original column. Press Enter to create the column. Just like before, make sure the new "Order Time" column has its Data type set to Time in the Column tools tab.

Best Practices and Tips

• Stick with Power Query if Possible: For data modeling and preparation, Power Query is faster, more efficient, and easier to manage long-term. Do your transformations there whenever you can.
• Double-Check Your Data Types: Whether using DAX or Power Query, always ensure your final 'Date' and 'Time' columns are set to the correct data types. A date column formatted as text won't sort correctly, and you won't be able to use it with most time intelligence functions.
• Use a Calendar or Date Table: Once you have a separate date column, the best next step is to create a dedicated Calendar Table and build a relationship to your new date column. This gives you much more analytical power, allowing you to slice data by week, quarter, year, or custom fiscal periods.

Final Thoughts

Splitting date and time into separate columns is a fundamental step in prepping your data for meaningful analysis in Power BI. Whether you prefer the straightforward clicks in Power Query or the flexibility of DAX formulas, you can get the job done quickly and unlock deeper insights by focusing on dates and times independently.

Ultimately, these steps - extracting, cleaning, and formatting data columns - are all part of a manual process that can drain hours from your week. We experienced the same frustration of wrestling with tools like Power BI to get basic insights, which is why we built Graphed. Instead of clicking through menus or writing formulas, you just connect your data sources and ask questions in plain English. For example, you can simply ask, "Show me last month's sales by date" or "What are our top 5 busiest hours of the day for support requests?" and our AI builds the visuals instantly, handling all the necessary datetime transformations for you in the background.