PowerQuery date time column not merging correctly

Question

I am currently trying to merge two queries using a column with date and time. Both columns are formatted the same, but Excel cannot seem to match up the dates and times correctly. I have tried forcing their "Locale's" and this does not seem to solve the problem. Below is a picture of how I set up the merge. 

Excel then merges the files and is unable to match the date/time stamps even though I have confirmed that 20 of them match up exactly. 

How do I get these date/times to combine correctly? 

Thanks!

Answer 1

I tweaked Herbert's (sorry for mistaken name in early reply) spreadsheet to demonstrate the need for rounding rather than using raw datetime values when comparing.

https://1drv.ms/x/s!Am8lVyUzjKfpnwH8jEKrxF5h3KzI

I added columns to show the full value of the datetime colums, showing the full 15 digits, 5 digits for days count and 10 digits for hours/minutes/seconds.

I rounded the datetime to seconds using MRound()

I added conditional formatting to highlight the equal values comparisons

I added a comparison of the 2 MRound values.

Thanks for the starting point.

Answer 2

Rohn007,

Thanks for delving into it more properly and deeply.

The next level is to apply it to the OPs Power Query application.

Although it could be oodles of other faults, let's assume his is an accuracy problem.

Power Query does not have an MRound() function.

My workaround is to convert the DaTm into total seconds,

round it off and then convert it back to DateTime.

Same link.

Regards,

Herbert (aka Harold)