Graphed LogoGraphed
Login

How to Split Date Column in Power BI

Cody Schneider•

Working with dates in Power BI can feel like a simple task until your data isn't quite in the format you need. A single column with both date and time is useful, but for deeper analysis - like comparing sales by month or traffic by day of the week - you need to break it apart. This guide will walk you through several easy methods for splitting a single date and time column into more valuable, separate columns for your reports.

Why Bother Splitting a Date Column?

Splitting a combined date-time column isn't just a formatting chore, it's the key to unlocking more specific and powerful insights. When your dates are separated into components like year, quarter, month, and day, you give yourself more flexible building blocks for analysis.

Here are a few common scenarios where splitting the column is essential:

  • Analyzing by Time Periods: You may want to see if sales are higher in Q4 than in Q1, or if website traffic spikes in January. Without a separate 'Month' or 'Quarter' column, creating these visuals requires complicated DAX formulas.

  • Comparing Performance by Day of the Week: An e-commerce store might want to know if they get more orders on Mondays or Saturdays. A 'Day of the Week' column makes this a simple drag-and-drop operation in a chart.

  • Filtering by Time of Day: If you're analyzing call center data, separating the time from the date allows you to see if call volume is heaviest in the morning or afternoon.

  • Simplifying Your Model: Creating helper columns in Power Query before you even start building visuals makes your DAX formulas simpler later on. Easy-to-read formulas are easier to maintain and troubleshoot.

In short, splitting your date column turns one limited piece of information into multiple, highly flexible dimensions for slicing and dicing your data.

Method 1: Using the Built-in 'Date' and 'Time' Features in Power Query

The simplest and most common method is to use the dedicated tools within Power BI's Power Query Editor. This technique keeps your original column intact while adding new columns with the specific parts of the date you need.

Let's walk through it step-by-step.

Step 1: Open the Power Query Editor

First, you'll need to open the transform window. From the main Power BI Desktop window, go to the Home tab on the ribbon and click on Transform data. This will launch the Power Query Editor, where you can shape and clean your data before it's loaded into your model.

Step 2: Select Your Date-Time Column

Find and click on the header of the column that contains both the date and time information. Once selected, the column will be highlighted.

Step 3: Duplicate the Column (Optional but Recommended)

While the following steps won't delete your original column, it's good practice to duplicate the column first. This gives you a backup and helps keep track of transformations. Right-click the column header and select Duplicate Column.

You can duplicate it twice - one to become your 'Date' column and one to be your 'Time' column.

Step 4: Extract the 'Date Only'

With one of your duplicated columns selected:

  1. Click on the Add Column tab in the ribbon.

  2. Find the 'From Date & Time' section.

  3. Click on the Date button and select Date Only from the dropdown menu.

Power BI will immediately create a new column containing just the date portion of your original column. You can rename this column to something clear, like "Order Date."

Step 5: Extract the 'Time Only'

This works the same way. Select the second duplicated column:

  1. Navigate back to the Add Column tab.

  2. Click the Time button this time, and select Time Only.

You’ll now have another new column containing just the time. Rename it to "Order Time."

Step 6: Extract Other Date Components (Year, Month, etc.)

Now that you have a clean "Date Only" column, you can easily break it down further. Select your newly created "Order Date" column.

  • Back in the Add Column > Date menu, you will see options like Year, Month, Quarter, Week, and Day.

  • You can click Year > Year to get the year number (e.g., 2023).

  • You can go to Month > Name of Month to get the full month name (e.g., "October").

  • You can go to Day > Name of Day to get the weekday (e.g., "Thursday").

By using these menu items, you can create all the date components you need for your report filters and visuals without writing a single line of code.

Method 2: Using 'Split Column by Delimiter'

Sometimes, your date information is imported as a plain text string instead of a true DateTime data type. When this happens, Power BI's 'Date' menu might be grayed out. In this case, the 'Split Column by Delimiter' tool is your best friend.

You can use this if your date looks something like this: 26/10/2023 15:45 in a text column.

  1. Select the Text Column: In the Power Query Editor, click on the column containing your text date.

  2. Choose 'Split Column': On the Home tab, click Split Column and then choose By Delimiter.

  3. Select the Delimiter: A dialog box will appear. Power BI is smart and will likely auto-detect the delimiter. In our example 26/10/2023 15:45, the delimiter between the date and time is a space. Select "Space" from the dropdown. Make sure to choose "At the left-most delimiter" if you only want to split the column into two parts (date and time). Click OK.

  4. Convert Data Types: Power BI will split your column into two new text columns. One will have 26/10/2023 and the other 15:45. Here's the critical final step: you need to convert their data types.

    • Click the icon to the left of the new date column's header (it usually looks like 'ABC') and select the Date data type.

    • Do the same for the new time column, but select the Time data type.

Once you’ve converted them to the correct data types, you can use Method 1 on the newly created 'Date' column to extract the year, month, and day.

Method 3: Creating Custom Date Columns with DAX

While handling this in Power Query is usually best practice, you might find yourself needing a calculated date part directly in your report. This is where DAX (Data Analysis Expressions) comes in handy. DAX formulas create calculated columns that are computed after the data is loaded.

This is done in the Report View or Data View, not Power Query.

Go to the Data View (the table icon on the left), select your table, and click on New Column in the ribbon. Then, you can use simple DAX formulas.

To Extract the Year:

Order Year = YEAR('Orders'[OrderDate])

To Get the Month Number (1-12):

Month Number = MONTH('Orders'[OrderDate])

To Get the Full Month Name (e.g., "October"):

Month Name = FORMAT('Orders'[OrderDate], "MMMM")

To Get the Full Day of the Week Name (e.g., "Thursday"):

Day Name = FORMAT('Orders'[OrderDate], "dddd")

Remember to replace 'Orders' and 'OrderDate' with your actual table and column names.

A Quick Tip on Sorting: If you create a "Month Name" column with DAX, Power BI will sort it alphabetically (April, August, December...). To fix this, create a "Month Number" column as well. Then, in the Data View, select your "Month Name" column, go to the Column tools in the ribbon, and click Sort by column. Choose your "Month Number" column, and your visuals will now sort correctly chronologically.

Best Practice: Always Use a Calendar Table

While splitting columns is great for ad-hoc analysis, the most robust and scalable way to handle dates in Power BI is to create a dedicated Calendar Table (or Date Dimension).

A calendar table is a separate table that contains a continuous list of dates along with all the components you need: Year, Quarter, Month Name, Month Number, Week Number, Day of Week, etc. You then create a relationship between your Calendar Table's date column and the date column in your data table (like 'Orders'[OrderDate]).

Using a calendar table:

  • Ensures consistency across your entire report.

  • Makes time-intelligence functions like TOTALYTD (Year-to-Date total) or SAMEPERIODLASTYEAR work flawlessly.

  • Centralizes your date logic instead of having dozens of calculated columns scattered across your data tables.

You can create a calendar table easily using DAX with functions like CALENDARAUTO() or CALENDAR(). It’s a small amount of work upfront that saves hours of headaches later.

Final Thoughts

Splitting a single date-time column into separate, manageable parts is a fundamental skill for anyone working in Power BI. Whether you use the simple tools in the Power Query Editor, split a text-based date by its delimiter, or apply specific DAX functions, breaking down your dates properly gives you a more powerful and flexible model for analysis.

Steps like a little data preparation are often just the beginning of a long reporting process. With modern tools, the goal is to get answers, not just rearrange data. At Graphed we help you skip the manual busywork by connecting directly to your data sources and automating report generation. Instead of clicking through menus to split columns and aggregate data, you can simply ask in plain English, "Show me a chart of our total sales by month this year," and get an interactive dashboard in seconds.