Power Query, Need Add Column of Time to show quarter hour intervals using rounddown or floor?

[greegan]

Good morning,

First of all thank you for reading and thank you for answering.
I'm using office 365 on a work computer with Windows 10.
I'm very new to Power Query, however aside from the M? language and the DAX formulas, I think I've picked it up pretty fast.

I have a time sheet with our employees inbound and outbound calls.

I need to identify the time in quarter intervals. I could use the Floor function in Excel in a table. And I may need to do that if PQ doesn't have a solution.

=FLOOR([@[start_time]],1/96)

This is what I'm using right now:

Time.From(Number.RoundDown([Time]),1/96)

Which appears to work fine.

I compare it with a Floor formula in a table. Give it a True and False on it matching the result of the query.

=IF([@[Quarter_Intervals]]=[@Column1],TRUE,FALSE)

Out of 7k records only one is false.

The Time showing to convert to the quarter shows 3:30:00 PM, and the PQ result is 3:15:00 PM

This is the only error. Can someone in the know shed some light on why this would happen?

Would I be better to use the Floor function in a table with my data and query the table?


Many Thanks

-- g

 

[Norie]

Try using this instead of Number.RoundDown.

= Time.From(Number.IntegerDivide(96*Number.From([Time]),1)/96)

 

[greegan]

Try using this instead of Number.RoundDown.

= Time.From(Number.IntegerDivide(96*Number.From([Time]),1)/96)

Thank you for the quick reply Norie.
What I have done to test and see if I have issues with more than just 3:30:00 PM, was to list in my table every minute of the day from 12:01:00 AM to 11:59:00 PM
With my M formula? it has something like 76 False resulting comparing the Floor function.

With your formula above it results in 10 as False matches. Better than mine for sure.

 

[greegan]

I believe i resolved this. Not the most efficient way I'm sure ...
Column A time in the query, I convert to decimals, then add half a minute to the time
Use Norie's formula in an add custom formula window

= Time.From(Number.IntegerDivide(96*Number.From([Time]),1)/96)

(sorry still not familiar with the correct jargon)
and then remove the added column with the extra 30 seconds.

I'm not sure why the floor formula is giving a false match still but I'm comparing the original time in column A to the query's result in B and they match 100%

Thank you Norie. i will mark this resolved.

 

[Norie]

Don't know why that's happening, when I compare the results of the formula I posted with the results from FLOOR in Excel they are exactly the same.

I did come up with this for Power Query and it seems to work.

#time(Time.Hour([Time]), Number.IntegerDivide(Time.Minute([Time]), 15)*15, 0)

 

[greegan]

I believe i resolved this. Not the most efficient way I'm sure ...
Column A time in the query, I convert to decimals, then add half a minute to the time
Use Norie's formula in an add custom formula window

= Time.From(Number.IntegerDivide(96*Number.From([Time]),1)/96)

(sorry still not familiar with the correct jargon)
and then remove the added column with the extra 30 seconds.

I'm not sure why the floor formula is giving a false match still but I'm comparing the original time in column A to the query's result in B and they match 100%

Thank you Norie. i will mark this resolved.

Okay so this is not resolved. Because my time sheet is giving me times in seconds, adding 30 will mess it up. Unless i can rounddown to the nearest minute. That might work.

 

[greegan]

Don't know why that's happening, when I compare the results of the formula I posted with the results from FLOOR in Excel they are exactly the same.

I did come up with this for Power Query and it seems to work.

#time(Time.Hour([Time]), Number.IntegerDivide(Time.Minute([Time]), 15)*15, 0)

Thank you again Norie. I may be using the above incorrectly but it gives me Error througout the column.
If I could use the Floor function to round the seconds off and then use your original code, it might work?

 

[Norie]

That formula should work provided you have a column named Time which has the data type Time.

 

[greegan]

That formula should work provided you have a column named Time which has the data type Time.

Norie ! Genius !
It took me a bit of time to get back to the file (at work) and then I realized your formula didn't require me to change time to decimals. This is awesome, all of my Floor numbers match with yours perfectly.
Thank you.

~ g