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

Hi vlhohol,

Thank you for posting your question in our community. I notice that it says "The selection has matched 0 out of the first 4000 rows". Based on my test, if there are [x] rows that match, the message would be "The selection has matched [x] out of the first 4000 rows" instead. 

In this case, I'd suggest that you may check your data and see if there is any other differences. For example the dates may match, but the hours or minutes are different. You may also share the spreadsheet with us via OneDrive for Business so that we could check it for you.

Best Regards,

Nathan

Answer 2

I have looked at all the instances that I want to match and they exist in both lists exactly. 

From List 1:

From List 2:

I will see if I can share the document.

Answer 3

Right, Harold thanks for taking my reply to the "next step".

You have to answer 2 questions:

1. what unit do you want to round your time to, seconds, minutes, quarter hours, hours
2. do you want to round up or round down.

Here are some more time rounding tips:

Three tips for rounding Excel time values

https://www.techrepublic.com/blog/windows-and-office/three-tips-for-rounding-excel-time-values/

Answer 4

Extract just the relevant parts, like the times and one or 2 other columns and share just that.

Take a look at the underlying value for those formatted timestamps.  I'm wondering if the values go down to seconds and that is why they match

@ The truth behind Excel dates, time and durationhttps://office-watch.com/2018/truth-behind-excel-dates/****Date handling in Excel can be confusing and it’s not your fault.  Microsoft could do a better job. Here’s a quick guide to how Excel handles dates and date arithmetic with the settings you should know.

.  *  What you see isn’t what’s saved               .  *  Excel date and times

.  *  Date and Time arithmetic                           .  *  Displaying dates and times

.  *  How Windows changes Excel dates         .  *  Time Duration in Excel 

ET MR Dates and Times.docx

Upload Example - Trouble Shooting - Share OneDrive File

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471?tm=1523189328156

**************************************

Answer 5

Rohn007 is exactly right.

Even though the displayed times might match to the second,

the real values might go down below/above an even second

and, if different by a fraction of a second, not match.

Use MROUND() in any time comparison.

http://www.mediafire.com/file/ztfe1nc3akogab9/04_08_19.xlsx/file

http://www.mediafire.com/file/id6cctja6wp57d3/04_08_19.pdf/file