PowerBI-DirectQuery - Separate Time From DateTime

Question

i currently have a DirectQuery set up on a SQL Server. There you will find field in the format "dd.mm.yy H:i:s".

Is it possible to separate the time from the date? So that I can set a filter?

Answer 1

If your column name is DateTime, you can separate Time from them in Power Query Editor. Just add a custom column with this below code shown-

= Time.From([DateTime])

OPTION 2

You can also split the value using Space (" ") which will separate Date and Time in 2 column in power query-

Output will be as below-

Answer 2

Unfortunately, what mkRabbani suggest does not work with DirectQuery connection - you cannot split column nor use "Time.From()" and preserve DirectQuery - Power BI will immediatelly suggest turnig into Import mode to apply the changes...

If you don't need it at the Power Query level but at the model level, you can create calculated column which will equal to your original Date-Time column. This calculated column then could be set to Date or Time data type.

Miro.

Answer 3

If we want to separate date and time from the DateTime columns in the DirectQuery, you can do it by changing the SQL query while importing the data.

Go to Power Query Editor and click on the settings icon in Applied Steps.

Data source setting

Then, click on advance options in the SQL server database setting and add the below SQL statement for your table. Change the column name based on your table column name

SELECT *,
     [CreateDate_Date] = CONVERT(date, [CreateDate]),
     [CreateDate_Time]  = CONVERT(time, [CreateDate])
FROM     [dbo].[Table] 

SQL Query

Choose the Server and Database name and click Ok