How to work with DateTime Formatting in Power BI?

by

User Question
I’m working with timestamp data in my Power BI report, and I’m struggling to separate dates from times. Some columns show full timestamps when I only need dates, while others need specific time formats. How can I handle different datetime formats effectively?

Data Model

We’ll work with a SalesTransactions table containing: TransactionID (Number), CustomerID (Number), TransactionDateTime (DateTime), LastModified (DateTime), Amount (Currency).

PowerQuery DateTime to Short Date Conversion

  1. Open Power Query Editor (by hitting Home > Transform Data)
  2. Select your datetime column (in our example: TransactionDateTime)
  3. Right-click and choose ‘Transform’ or use the Transform tab.
  4. You have two main approaches for this step:

Using the GUI (Graphical Interface):

  • In the “Transform” tab of the ribbon, locate the “Data Type” dropdown (shows ABC123 icon)
  • Click to reveal options:
    a) Choose “Date” (calendar icon) to remove time portion completely
    b) Choose “Date/Time” to keep both but change format
  • For more specific formatting: Transform tab ? Format ? select preferred format

Using Custom Columns

  • Go to “Add Column” tab in the ribbon
  • Click “Custom Column”
  • In the dialog box, enter: New column name: FormattedDateTime Custom column formula: = DateTime.ToText([TransactionDateTime], "dd-MMM-yyyy")

Key Important Differences:

  • Data Type conversion modifies the original column
  • Custom Column creates a new column, preserving original data
  • Custom Column offers more format control
  • Data Type conversion is more memory-efficient (no data duplication)

Converting DateTime to Date in Power BI Desktop

Sometimes you need to cast datetime as date after loading data:

  1. Create a new calculated column:
   Transaction_Date = DATE(
       YEAR('SalesTransactions'[TransactionDateTime]),
       MONTH('SalesTransactions'[TransactionDateTime]),
       DAY('SalesTransactions'[TransactionDateTime])
   )

Extract Time from DateTime Power BI

To split time components in DAX:

Add a calculated column for time extraction:

   Transaction_Time = TIME(
       HOUR('SalesTransactions'[TransactionDateTime]),
       MINUTE('SalesTransactions'[TransactionDateTime]),
       SECOND('SalesTransactions'[TransactionDateTime])
   )

Troubleshooting DateTime Formatting

  • If Power Query shows “Error” in datetime columns, check for inconsistent formats in source data
  • For “Cannot convert DateTime” errors, ensure regional settings match your data format
  • When time zones cause issues, use DateTime.FromText() with explicit formatting in Power Query
  • If calculations return unexpected results, verify that DateTime columns aren’t stored as text
  • For performance issues with datetime operations, consider creating separate date and time columns during the load process

The key to mastering datetime formatting is choosing the right transformation point – either during data load with Power Query or post-load with DAX, depending on your specific requirements and performance needs.