Date/Time Columns Lose Formatting with DirectQuery in Incremental Refresh

Hey bIearner83,

Quick Fix for DirectQuery Date Hierarchy Issue

What's Happening

DirectQuery real-time mode bypasses Power Query transformations, so your date columns lose their hierarchy recognition. This is a known limitation.

Best Solutions (Pick One)

Option 1: Separate Date Table (Recommended)

• Create a simple date dimension table in Import mode
• Relate it to your DirectQuery fact table
• Use the date table fields for slicers instead of your fact table dates
• This gives you full hierarchy control and works perfectly with DirectQuery

Option 2: Fix at SQL Source

• In your SQL query, explicitly cast date columns: CAST(InvoiceDate AS DATE) AS InvoiceDate
• Make sure your database columns are proper DATE/DATETIME types
• Sometimes DirectQuery picks up formatting better from the source than Power Query

Option 3: Adjust Refresh Strategy

• Reduce your real-time window (maybe refresh every 2-4 hours instead)
• Keep more data in Import mode, less in DirectQuery
• You might not need real-time for all your historical data anyway

Quick Test

Try Option 1 first - create a basic date table and see if that solves your hierarchy problem. It usually does and doesn't require changing your source data.

Most people find the separate date table approach works best because it's reliable and doesn't depend on DirectQuery's quirky behavior with data types.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best regards,
Jainesh Poojara / Power BI Developer