Separating a date/ time column to date column and time column without breaking query folding

[Dana]

Hi,
I am importing data from a postgres db.
I have a date/time column - which I would like to split into date column and time column.
I would like to implement incremental refresh and therefore trying not to break the query folding.
I can change the Data type of the column from data/time to date without breaking the query folding.

However all the approaches I have tried for the time column have broken the query folding.
Basically once I change the Data Type to Time - the query folding breaks.

Changing the Data Type in the model section didn’t work as I wasn’t able to establish a relationship with the time table.

Any suggestion how to make it work?

I attached a basic PBIX with my data example.pbix (207.5 KB)

Thanks

[Melissa]

Hi @Dana,

See if you can add a Time as Number without breaking query folding.
So in your fact table:

Table.AddColumn(#"Filtered Rows", "Time", each 
    Number.From(DateTime.Time([reported_date])), 
    type number
)

Repeat for the [Time] column in your Time dimension table, so you can create a relationship on that.
I hope this is helpful.

[Rajesh]

Hi @Dana

Without changing anything in Power Query, Create a calculate column using DAX.

[Dana]

Thanks @Rajesh,

It seems that adding a time column in the Data section using DAX does not produce the desired results. The “reported_time” field in the visual ends up being only 12:00AM

While the values in the table are various times

exampleR.pbix (240.0 KB)

[Rajesh]

@Dana

example.pbix - reported_date is datetime type filed in First file.
exampleR.pbix - reported_date is only date type field in Second file

Create time filed from DateTime Filed not from Date filed.

Hope this clarifies…

[Dana]

Hi @Melissa,

I liked your idea - but the query folding broke once I added the column.

Any ideas what I missed?

[Dana]

Thank you for replying so quickly @Rajesh.

I only changed the display on the field - the Type was still Date/Time

[Melissa]

Hi @Dana,

You didn’t miss a thing!
I can’t test if query folding breaks, sorry about that, could you please give this a try?
Thanks

Table.AddColumn(#"Filtered Rows", "Time", each 
    Number.From( Time.From( [reported_date] )), 
    type number
)

[Rajesh]

@Dana

Now I got it.

I created new Date column and change the relationship. PFA Pbix file

If you are using relation with reported_date it selects only 12:00 AM records.

exampleR (1).pbix (248.4 KB)

[AntrikshSharma]

@Dana Here is something you could try:

Duplicate the column > load to model > change the data type to time ( need to do this every time you refresh the data )

and you will still have query folding, otherwise, what Rajesh suggest is also a good alternative.

[Dana]

@Melissa - thank you for the alternative suggestion but that broke the query folding as well. It was worth a try.

[Dana]

@Rajesh - thank you for taking another look at my issue.
However the relationship between the tables is not working when I try to display any data.
For example if I create a slicer using the time field from the time table - the table which currently displays data becomes empty

Visual before selecting from slicer

Selecting 12.00 AM still shows all values in reported_time

and selecting any other value - results in no data in the table.

It nearly feels that when changing data types in the Data view - the relationships don’t seem to work as expected.

[Rajesh]

Hi @Dana

Similar to Date. Create new column for Time and change the relation

FYR - Attached PBIX file

exampleR (1).pbix (236.5 KB)

[Dana]

@Rajesh - thank you for persisting in helping me out.