Working with timestamps and a Date table..?

[michellepace]

Hello,

A while back I joined @Melissa on her wonderful thread Extended Date Table (Power Query M function) . Oh good I thought to myself, I will never ever have to worry about a Dates table again, I’ve got the perfect one! Except now am working with timestamps on AUDIT data.

Question: If I have a timestamp what should I do?

Option 1: Do I convert my “Date” in my Dates table to be a time stamp (so “Timestamp”) and generate a new row for each minute of each day and then map “Audit.event_timestamp to Dates.Timestamp” and then work from there?

Option 2: Or, should I rather create a new “Times” table (representing each minute in 24 hours) and map AUDIT.event_time to TIMES.time ? Whilst obviously keeping the mapping of AUDIT.event_date to Dates.Date as shown in the pic.

Options 3: Is there another better option

Thanks very much in advance,
Michelle

[Melissa]

Hi @michellepace,

From a granularity (and performance) standpoint it’s considered a best practice to split date and time, so option 2. Include a Date- and separate Time dimension table in your model.

You can find an example here in the M code showcase.

Time Table (Power Query M function)

Hi everyone, @DavidNealon thought it would be a nice addition if there was a Time Table Power Query M code function available in this category as well. Now I can’t take any credit because it was designed by Gina Meronek, you can find it and the full article she wrote here. When invoked the function creates a Time Table at a granularity of Minutes. If your requirement is different that can easily be fixed. Note that other changes might be required as well but to give you some ideas: For Hourl…

Cost of datetime values.

Separate date and time in PowerPivot (and BISM tabular) - SQLBI

I hope this is helpful

[EnterpriseDNA]

Hi @michellepace , did the response provided by @Melissa help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

[EnterpriseDNA]

Hi @michellepace, we’ve noticed that no response has been received from you October 22.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @michellepace, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.

[michellepace]

Super!!! Thanks VERY much @Melissa