---
title: "How do I handle mixed timestamp formats in PowerBI?"
url: "https://dm0bkdes5q7a2ls.productfruits.help/en/article/how-do-i-handle-mixed-timestamp-formats-in-powerbi"
language: "en"
last_updated: "2026-05-15T20:56:21.496Z"
---

# How do I handle mixed timestamp formats in PowerBI?

Handling mixed timestamp formats (e.g., some logs in MM/DD/YYYY, others in YYYY-MM-DD, or some including milliseconds while others don't) is a common challenge when combining logs from different Spirion Agent versions or different operating systems (Windows vs. Mac/Linux).

If you try to convert a mixed column to "Date/Time" all at once, Power BI will return **Errors** for any format it doesn't recognize.

Here is the professional approach to normalizing mixed timestamps using Power Query:

### 1\. The "Try and Catch" Strategy (Recommended)

This is the most robust method. You attempt to parse the date using one format, and if it fails, you try the second format.

**How to do it (Custom Column):**  
In Power Query, go to **Add Column** > **Custom Column** and use this logic:

```
let 
    // Try Format 1 (ISO Standard)
    date1 = try DateTime.FromText([TimestampString], "en-US"),
    // Try Format 2 (Alternative)
    date2 = try DateTime.FromText([TimestampString], "en-GB")
in 
    if date1[HasError] = false then date1[Value] else date2[Value]
```

-   **Why this works:** It allows the script to be flexible. If the Windows agent uses US formatting and the Linux agent uses ISO, this column will correctly interpret both.

### 2\. Using "Using Locale" (The Easy Way)

If the issue is simply that one file is from a UK server (`DD/MM`) and another is from a US server (`MM/DD`), you can use the Locale setting.

-   **Action:** Right-click the column > **Change Type** > **Using Locale...**
-   **Selection:** Choose **Date/Time** and then select the **Region** that matches the majority of your logs. Power BI is much smarter at guessing formats when it knows the intended region.

### 3\. Normalizing via "Replace Values"

Sometimes the "mixed" format is just a slight character difference (e.g., some logs use a comma `,` before milliseconds and others use a period `.`).

-   **Action:** Before converting to Date/Time, use **Transform** > **Replace Values**.
-   **Example:** Replace all `,` with `.` in your timestamp column.
-   **Result:** This creates a uniform string that the standard "Change Type" tool can handle easily.

### 4\. Handling "Unix Epoch" vs. "Human Readable"

Rarely, you might see some internal Spirion logs using Unix timestamps (long strings of numbers like `1715800000`).

-   **The Fix:** Use a conditional column.
-   **Logic:** `if Text.Length([TimestampString]) > 10 then [Convert Unix Logic] else [Standard Parse]`.
-   *(Note: Standard Spirion IDF/EPS logs almost always use Human Readable formats, but database-level logs may differ.)*

* * *

### **Updated M Code for Mixed Formats**

Add this step to your script to handle the two most common Spirion timestamp variations:

```
    // Add this after your "SplitHeader" step
    NormalizeTimestamp = Table.AddColumn(SplitHeader, "FinalTimestamp", each 
        let
            // Attempt 1: Standard ISO (2024-05-15)
            AttemptISO = try DateTime.FromText([TimestampString], [Format="yyyy-MM-dd HH:mm:ss", Culture="en-US"]),
            // Attempt 2: US Standard (5/15/2024)
            AttemptUS = try DateTime.FromText([TimestampString], [Format="M/d/yyyy h:mm:ss tt", Culture="en-US"])
        in
            if AttemptISO[HasError] = false then AttemptISO[Value] 
            else if AttemptUS[HasError] = false then AttemptUS[Value] 
            else null // Returns null if both fail for manual inspection
    ),
    
    // Change the type of the new column
    FinalType = Table.TransformColumnTypes(NormalizeTimestamp,{{"FinalTimestamp", type datetime}})
```

### **SME Summary Checklist for Mixed Timestamps:**

1.  **Don't Force It:** Never just click "Change Type" if you see multiple formats; you will lose data to errors.
2.  **Clean First:** Use "Replace Values" to fix delimiters (commas, dots, slashes).
3.  **Use** `try` Logic: Use the `try...otherwise` pattern in M code to cycle through known formats.
4.  **Check for Nulls:** After running your logic, filter for `null` in your new timestamp column. If you find any, it means you've discovered a *third* format you need to add to your script.

**Summary:** Use the `try DateTime.FromText` logic in a Custom Column to attempt multiple parsing formats. This ensures that logs from different regions or agent versions are normalized into a single, sortable timeline without generating errors.