How to create a calendar table with second granularity?

[webportal]

I've built a calendar table using Power Query for the whole year of 2022 using second granularity.

Basically, by "cross joining" a calendar table with day granularity (365 rows) with a time table (24*60*60 rows).

The table contains 31.536.000 rows, all distinct values for column DateTime.

Now, after relating this table with a transacional table, the visuals do not show any values.

Here's a sample of the transactional table:

Although all these timestamps are present in the calendar table, I get an empty matrix when trying to visualize the results.

[webportal]

@Anonymous 

I've managed to relate the DateTime table with the transactional table by converting the timestamp into integers (as the difference in seconds to the 1st datetime). This worked perfectly.

I also separated the simple Date and Time into two different tables with the great advantage - as you well refer - to decrease the size of the model.

Thanks for helping!

[webportal]

@Anonymous 

Yes, both tables have timestamps, that's why I don't understand why the relationship doesn't work.

Thanks for the tip, but what if you want to have continuous visuals? Eg: a line chart for a certain week where you may scroll horizontally to the second?

[Anonymous]

My guess is that the matching on the timestamps simply isn't lining up (although it should - datetimes are stored as numbers under-the-sheets so I'm surprised it's not working).  I'd try the "resolve-it-to-yyyymmddhhmmss-format-and-join-on-that" approach.

As far as creating visualizations (if you have separate date and time dimensions) you could simply stack your date fields on the axis shelf and then stack your time fields underneath them and the drill-down/expand-down should work with no issues.

[Anonymous · Accepted solution]

In the Transaction table does the column you're creating the relationship on have timestamps as well?  If not then you won't get any matches because the Dates aren't lining up to the Timestamps.

Generally speaking in data modeling we keep our Dates and Times separate.  First, it cuts down on data volumes (only 86400 rows in the Time table and 365/366 rows per year in the Date table). 
I would try that - separate your Date and Time dimension tables and separate your Dates and Times in the transaction table.  Still gives you the capability to drill-down to the second level without super-sizing your model.