Graphed LogoGraphed
Login

How to Combine Two Date Columns in Power BI

Cody Schneider•

Trying to analyze performance by the hour when your date is in one column and your time is in another? It's a common and frustrating data formatting problem. Fortunately, Power BI provides several straightforward ways to combine them into a single, usable datetime column. This guide will walk you through a few different methods, from a simple point-and-click approach to using a basic formula.

Why Combine Date and Time Columns in the First Place?

Before jumping into the "how," let's quickly touch on the "why," because it’s important. Keeping date and time separate can limit your analysis and make creating certain visuals a major headache.

Here’s what you unlock with a unified datetime column:

  • Accurate Chronological Sorting: If you try to sort by a date column and then a time column, you’re not getting a true chronological view of events. A single datetime column ensures every event, from an order placed at 11:59 PM to another at 12:01 AM the next day, is perfectly ordered.

  • Time-Based Calculations: Want to calculate the exact duration between a support ticket's submission time and its resolution time? Or find the average time it takes for an order to be shipped? You need a single datetime value for both the start and end points to do these calculations easily.

  • Granular Trend Analysis: A date column lets you see daily trends, but a datetime column lets you zoom in further. You can analyze orders by the hour of the day, website traffic by the minute, or see what times your sales team is most active. This is perfect for identifying peak performance hours you'd otherwise miss.

In short, combining them makes your data much more flexible and powerful for real-world analysis.

Getting Started: Open the Power Query Editor

Every method we'll cover, except for the DAX approach, takes place inside the Power Query Editor. This is Power BI's built-in tool for cleaning, transforming, and preparing your data before it gets loaded into your report model.

To open it, simply click on the Transform data button on the Home ribbon in your main Power BI Desktop window. This will launch a new window where all the data magic happens. Let's assume you've already loaded a table with separate date and time columns, maybe named something like OrderDate and OrderTime.

Method 1: Using "Column From Examples" (The Easiest Way)

This is by far the most intuitive and beginner-friendly method. You literally show Power BI what you want, and it figures out how to do it for you. No formulas, no complex steps.

It’s a fantastic feature that feels a bit like magic and is perfect for this exact task.

Step-by-step Instructions:

  1. Select Your Columns: In the Power Query Editor, hold down the Ctrl key and click on the headers for both your date and time columns to select them. It helps to select the date column first, but it's not strictly necessary.

  2. Choose "Column From Examples": Navigate to the Add Column tab in the ribbon. On the far left, you’ll see an option called Column From Examples. Click on that.

  3. Provide an Example: A new, blank column will appear on the right side of your table. In the first row of this new column, start typing what you want the combined output to look like. For example, if your first row has "1/15/2024" in the date column and "9:30 AM" in the time column, you would type 1/15/2024 9:30 AM into the new column and press Enter.

  4. Let Power BI Do the Work: As soon as you press Enter, Power BI will analyze your input, look at the columns you selected, and automatically fill in the rest of the column with the correctly combined data. It intelligently understands you want to merge the values from those two columns with a space in between.

  5. Confirm and Set Data Type: If everything looks correct, click OK at the top. Power Query will create the new column and write the M code for the operation in the background (you don't have to touch it!). The final, crucial step is to set the proper data type. Click on the icon next to your new column’s header (it might look like "ABC") and select Date/Time. This tells Power BI to treat the new column as a true datetime value, not just text.

Method 2: Using "Merge Columns" (Quick and Reliable)

The Merge Columns feature is another straightforward, no-code option. It works just like it sounds: it takes two or more columns and merges their contents in the order you specify.

Step-by-step Instructions:

  1. Select Columns in Order: In the Power Query Editor, select your date and time columns. The order you select them in matters here! Click the date column first, then hold Ctrl and click the time column.

  2. Find "Merge Columns": You can find this option in two places. For creating a new column (which is usually the safest option), go to the Add Column tab and click Merge Columns. To modify your existing columns, go to the Transform tab.

  3. Configure the Merge: A new dialog box will pop up. For the Separator, choose Space from the dropdown menu. It's important to include a separator, otherwise, you'll get a jumbled mess like 11520249:30AM. Give your new column a descriptive name, like "OrderDateTime," and click OK.

  4. Change the Data Type: Just like with the previous method, Power BI initially creates the new column with a Text data type. You must change it. Click the "ABC" icon on the new column header and change the type to Date/Time.

Method 3: Using a Simple DAX Formula (for Calculated Columns)

Sometimes you might want to combine columns directly in your data model after leaving the Power Query Editor. This is where DAX (Data Analysis Expressions), Power BI’s formula language, comes in. This method is done in the main Power BI report view, not Power Query.

Creating a calculated column with DAX is incredibly simple for this task, as Power BI is smart enough to understand that adding a date and a time value should result in a datetime value.

Important Note: This method works best when your Date column has a Date data type and your Time column has a Time data type. If they are stored as text, you should clean them up in Power Query first.

Step-by-step Instructions:

  1. Navigate to Data View: In Power BI Desktop, click on the Data icon (it looks like a table) in the left-hand pane.

  2. Create a New Column: Go to the Column tools tab in the ribbon and click New column.

  3. Enter the Formula: An M-style formula bar will appear. Enter the following simple formula, replacing 'YourTable'[DateColumn] and 'YourTable'[TimeColumn] with the actual names of your table and columns:

  4. Format the Column: Press Enter to create the column. Power BI will perform the calculation. Finally, with the new column selected, go to the Column tools ribbon again and use the format dropdown to select a Date Time format that suits your needs (e.g., m/d/yyyy h:mm:ss AM/PM).

Troubleshooting Common Issues

Things don't always go perfectly. If you run into errors, it’s almost always because of an incorrect data type. Here are a couple of common stumbling blocks:

  • Data Type Errors: The most common issue is trying to combine columns that haven't been correctly identified as Date and Time types. If either of your columns has the "ABC" (Text) or "123" (Whole Number) icon, Power Query will struggle. Always change their data types to Date and Time before attempting to merge them.

  • Locale and Regional Settings: Sometimes, Power BI might misinterpret dates due to regional settings (e.g., confusing MM/DD/YYYY with DD/MM/YYYY). If you're getting conversion errors, you can right-click the column header, choose Change Type > Using Locale..., and specify the format for your data.

  • The "1899" Date Problem: Occasionally, a time column might actually be a full datetime column where the date part is a default value, like "12/30/1899". This can cause issues with the DAX method. In this case, it's best to stay in Power Query. You can right-click the column, go to Transform > Time Only to remove the unwanted date part, and then proceed with merging.

Final Thoughts

Combining date and time columns is a fundamental data prep task in Power BI. Whether you prefer the intuitive 'Column From Examples' feature, the quick 'Merge Columns' tool, or a simple DAX formula, there's a method that fits your comfort level and gets the job done efficiently. The key is simply to ensure your data types are set correctly before you begin.

This process of cleaning and transforming data is just one step in getting to meaningful insights. Often, it's part of a longer, repetitive cycle of connecting to data, cleaning it up, and manually building reports. At Graphed, we felt this pain ourselves, which is why we built a tool to automate it. We make it easy to connect your sources - like Google Analytics, Shopify, and Salesforce - and then create dashboards and reports just by asking questions in plain English, getting you from raw data to a real-time dashboard in seconds, not hours.