Forum Discussion
Restrictions with Date/Time Transformations
Hi niamhks,
Unfortunately, it is not possible to extract only date or time part from a datetime field in Power Query with Direct Query connection. This is the limiattion of Direct Query. But we are able to extract Hour/Minute/Second from DateTime.
As a workaround, a new created calculated column in data view can also be used in report page. It works the same as custom column created in Power Query.
Best regards,
Yuliana Gu
Hi niamhks,
Duration() is not supported in Direct Query. To achieve the same result, you could create calculated columns using DAX in Report View mode.
second =
DATEDIFF ( Test4[search_start_date], Test4[search_complete_date], SECOND )
H:m:s =
RIGHT ( "0" & INT ( Test4[second] / 3600 ), 2 )
& ":"
& RIGHT (
"0"
& INT ( ( Test4[second] - INT ( Test4[second] / 3600 ) * 3600 ) / 60 ),
2
)
& ":"
& RIGHT (
"0"
& INT (
Test4[second]
- INT ( Test4[second] / 3600 )
* 3600
- INT ( ( Test4[second] - INT ( Test4[second] / 3600 ) * 3600 ) / 60 )
* 60
),
2
)
Best regards,
Yuliana Gu
Thank you for your suggestion.
I am wanting to split the column in the query as want to then use that column in the report as opposed to creating this column in the report.
My understanding is that it isn't possible just thought i'd throw the question out there to see if anyone knew of a way!
Hi niamhks,
Unfortunately, it is not possible to extract only date or time part from a datetime field in Power Query with Direct Query connection. This is the limiattion of Direct Query. But we are able to extract Hour/Minute/Second from DateTime.
As a workaround, a new created calculated column in data view can also be used in report page. It works the same as custom column created in Power Query.
Best regards,
Yuliana Gu