How to Convert Timestamp to Date in Power BI

Working with timestamps in Power BI can sometimes feel like trying to read a different language, with long strings of numbers and letters like 2024-07-15T10:30:55.123Z cluttering up your data. The good news is that converting these into clean, simple dates is a fundamental skill that’s easy to master. This article will walk you through the best methods for turning messy timestamps into analysis-ready dates using 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 Bother Converting Timestamps to Dates?

You might wonder if it's worth the effort to change your timestamps. The answer is a resounding yes, for a few critical reasons:

• Clarity and Readability: A simple date like "07/15/2024" is instantly understandable for anyone reading your report. A full timestamp is not. Clear reporting is effective reporting.
• Data Aggregation: To analyze trends, you need to group - or aggregate - your data by day, week, month, or year. You can't do this effectively with a unique timestamp for every row. Converting to a date allows you to easily summarize sales by month, website traffic by week, or new leads per day.
• Creating Relationships: Power BI’s real power comes from its data modeling capabilities. To use powerful time-intelligence functions (like year-over-year growth), you need a dedicated calendar table. You can only create a clean relationship between your data and a calendar table using a common date column, not a unique timestamp.

In short, converting timestamps unlocks much of the analytical power you need for meaningful business intelligence.

The Simplest Way: Using the Power Query Editor

For most situations, the Power Query Editor is the quickest and most intuitive place to handle date transformations. It’s a visual interface that records your steps, making it easy to track and modify your changes later.

The best practice here is not to change the original column, but to create a new one. This preserves the original timestamp in case you need to analyze the time component later, such as figuring out your peak business hours.

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-by-Step Guide in Power Query

Let's say you have a table of website user sessions with a "Session Start Time" column.

1. Open Power Query: From the Power BI Desktop main screen, click on the Transform Data button in the Home ribbon. This will launch the Power Query Editor.
2. Select Your Timestamp Column: In the Power Query window, find and click on the header of the column you want to convert (e.g., "Session Start Time").
3. Go to the "Add Column" Tab: At the top of the window, click on the Add Column tab. This ensures you'll create a new column, leaving your original data intact.
4. Extract the Date: In the "From Date & Time" section of the ribbon, you'll see a group of icons. Click on the Date icon, and from the dropdown menu, select Date Only.

That's it! Power Query will instantly add a new column to your table containing just the date part of the original timestamp. You can then double-click the new column's header to rename it to something more descriptive, like "Session Date."

For More Control: Using Power Query M Functions

If you prefer writing formulas or need to embed this logic within a more complex custom function, you can accomplish the same thing using Power Query’s M language. The best way to do this is with a Custom Column.

Creating a Date Column with DateTime.Date

1. Follow the first two steps from the previous section to open Power Query and select your source table.
2. Go to the Add Column tab and click on the Custom Column icon.
3. In the Custom Column dialog box, give your new column a name, such as "OrderDate".
4. Enter the following formula in the formula box, replacing [YourTimestampColumn] with the actual name of your column:

DateTime.Date([YourTimestampColumn])

1. Click OK.

This M function, DateTime.Date(), is designed specifically for this task: it takes a datetime or datetimezone value as an input and returns only the date part. It's the M-code equivalent of the button click we performed earlier.

Alternative Method: Using DAX for On-the-Fly Conversions

While Power Query is the recommended place for data shaping, there are times you might need to create a date conversion directly within your data model. This is where DAX (Data Analysis Expressions) comes in handy. DAX is used to create calculated columns and measures that exist on top of your imported data.

When to Use DAX vs. Power Query for This?

Rule of thumb: If you can do it in Power Query, do it there. Power Query transformations are applied once at data refresh, resulting in a cleaner and often more performant model. Use a DAX calculated column only when your source data cannot be modified or when the logic depends on other DAX calculations within your model.

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

Creating a Calculated Column with DAX

Here’s how to convert a timestamp using a DAX calculated column, with what is arguably the most efficient formula for the job.

1. In Power BI Desktop, go to the Data view by clicking the table icon on the left-hand navigation pane.
2. Select the table that contains your timestamp column from the Fields pane on the right.
3. From the Table Tools ribbon at the top of the screen, click New Column.
4. In the formula bar that appears, enter the following DAX formula, replacing 'TableName'[TimestampColumn] with the reference to your table and column.

The Easiest DAX Method: Using INT()

Order Date = INT('TableName'[TimestampColumn])

This works because Power BI, like Excel, stores dates and times as numbers. The integer part represents the number of days since a base date (for Power BI, it's 30/12/1899), and the decimal or fractional part represents the time of day. The INT() function simply strips away the decimal portion, leaving you only with the whole number that represents the date.

After you create the column, make sure to format it correctly:

• Select the new calculated column.
• In the Column tools ribbon that appears, click the Data type dropdown and select Date.
• You can also pick a specific format from the Format dropdown (e.g., m/d/yyyy).

Troubleshooting: Handling Tricky Timestamp Formats

Sometimes, your data isn't perfectly formatted, and Power BI doesn't automatically recognize your timestamp. Here are solutions for two common culprits.

Unix Timestamps (Epoch Time)

A Unix timestamp represents the number of seconds (or sometimes milliseconds) that have passed since midnight on January 1, 1970 (UTC). It’s just a long integer, which Power BI won't recognize as a date on its own.

To convert a Unix timestamp in Power Query:

1. Open the Add Column → Custom Column dialog.
2. Enter a name for your column.
3. Enter this magic formula (assuming your timestamp is in seconds):

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [YourUnixTimestampColumn])

Let's break that down:

• #datetime(1970, 1, 1, 0, 0, 0) creates the starting point (the beginning of Unix time).
• #duration(0, 0, 0, [YourUnixTimestampColumn]) takes the value from your column and converts it into a duration of seconds.
• Adding them together gives you the final, correct date and time. From there, you can use the Date Only transformation to get just the date.

Note: If your Unix timestamp is in milliseconds, you'll need to divide it by 1000 first: ([YourTimestampColumn]/1000).

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

Timestamps Stored as Text

If your timestamp is coming in as text and Power BI doesn’t automatically recognize it, don’t panic. This often happens if the format is non-standard. The most robust way to fix this is using the Change Type > Using Locale feature in Power Query. This feature tells Power BI to interpret the text based on a specific country or region’s formatting rules.

1. Right-click the header of the column that’s stored as text.
2. Go to Change Type → Using Locale…
3. In the dialog that appears, set the Data Type to Date/Time.
4. For the Locale, choose the country that matches the data's format. For example, if your date is DD/MM/YYYY, you might pick "English (United Kingdom)". If it's MM/DD/YYYY, you could use "English (United States)".
5. Click OK. This tells Power BI how to properly read the text, converting it into a true datetime value that you can then work with.

Final Thoughts

Converting timestamps into dates might seem like a small step, but it's a foundational part of building clear, robust, and insightful reports in Power BI. Whether you use the simple Power Query UI, a quick M function, or a DAX calculated column, cleaning up your date fields makes all subsequent analysis smoother and more powerful.

Data preparation is a crucial first step, but it’s often just a means to an end: getting fast, reliable answers. This is an area we designed Graphed to simplify radically. Instead of an eleven-step process to transform data and build reports, imagine just connecting your sources and asking in plain English. With Graphed, you can simply say, "show me my sales revenue by date for last quarter," and get an interactive, real-time dashboard in seconds. We automate the entire pipeline - from data connection and transformation to visualization - allowing you to focus on the insights, not just the setup process.