Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Using DirectQuery to pull a SQL query
The format of the date column is datetime '2023-01-01 00:00:00.000'
In SQL if I use the CONVERT below, I get the results wanted:
SQL: convert (date, (date))
Results: 2023-01-01
However, when I put my SQL into PBI, the date column is displayed in its original datetime format.
Are there any solutions or workarounds that don't involve Import Mode?
Thank you for your help!!
Solved! Go to Solution.
Another question, are you connecting to a table/view or are you using a stored procedure/sql command directly in Power BI? This can only be done if you are connecting to an actual structure and not using a sp or sql command within Power BI.
The correct fix for this situation is to create a calculated column with DATEVALUE() formula in DAX. Spent hours trying to troubleshoot but this one finally worked. This is the only solution that worked for me in DirectQuery mode.
No other datetime to date conversions in PBI Desktop were effective since the sql query getting generated kept ignoring this data type conversion. However, using DATEVALUE(), it effectively creates the correct SQL query when fetching data and even works if you have joint this calculated column to columns from other DirectQuery tables.
See how it creates the SQL:
SELECT CAST(CAST(CAST([t3].[modifiedon] AS VARCHAR(4000))AS DATE) AS DATETIME) AS [_modifiedon1 (DateOnly)]
FROM table1 AS [t3]
The correct fix for this situation is to create a calculated column with DATEVALUE() formula in DAX. Spent hours trying to troubleshoot but this one finally worked. This is the only solution that worked for me in DirectQuery mode.
No other datetime to date conversions in PBI Desktop were effective since the sql query getting generated kept ignoring this data type conversion. However, using DATEVALUE(), it effectively creates the correct SQL query when fetching data and even works if you have joint this calculated column to columns from other DirectQuery tables.
See how it creates the SQL:
SELECT CAST(CAST(CAST([t3].[modifiedon] AS VARCHAR(4000))AS DATE) AS DATETIME) AS [_modifiedon1 (DateOnly)]
FROM table1 AS [t3]
I am using a sql query directly in pbi. Good to know that it isn't an option this way. Thanks for your reply.
The only option may be to use 'import mode' and a scheduled refresh.
Have you tried to change the data type from Date/Time to Date in Power Query? You can do this by selecting the column and changing the data type in the transform tab.
Hello,
Indeed I have. It displays the message 'This step is not supported in DirectQuery mode. Switch to import.'
Another question, are you connecting to a table/view or are you using a stored procedure/sql command directly in Power BI? This can only be done if you are connecting to an actual structure and not using a sp or sql command within Power BI.
I am using a sp. Tested on an existing table and the outcome was what I needed.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
86 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
110 | |
97 | |
71 | |
67 |