Question

What is the best approach to convert a Date/Time column to a Date data type in Power Query while using DirectQuery from SQL?

I'm working with a Power BI report that connects to an SQL database using DirectQuery. One of the columns is in Date/Time format, but I need it as a Date-only type for filtering and calculations. Since DirectQuery has limitations on certain transformations, what is the most efficient way to achieve this conversion? Should it be handled in SQL before the data reaches Power BI, or is there a Power Query workaround that preserves performance?

Answer 1

For DirectQuery, the best way to convert a Date/Time column to Date is at the SQL level for performance.

Best Approaches:

SQL Conversion (Recommended):

SELECT CAST(DateTimeColumn AS DATE) AS DateOnlyColumn FROM TableName;

• Efficient, preserves query folding, avoids Power Query limitations.

Power Query with Native Query:

• Use a custom SQL statement in Power Query to ensure SQL handles the conversion.

Avoid Power Query Transformations in DirectQuery as they may break query folding and hurt performance.

Best Practice: Always convert in SQL before data reaches Power BI.