Cell values changing when files are combined

[RandomDenny]

I'm pulling two identically formatted excel files from a SharePoint folder and combining them in Power Query.

When the files are combined the date values in the 2nd file are getting replaced with values that make no sense to me. All of the other fields match the values in the excel doc. 

If I remove File 1 so that File 2 is all that remains then the date value shows as expected. So it's not an issue with the file.

Rows 52, 54 and 56 map to the excel rows highlighted in yellow in the 2nd screenshot. You can see that it's only the date values that are getting altered.

Any ideas on what might be causing this and more importantly how to stop it?

Thanks in advance

[jennratten]

Do the dates appear as yyyy/mm/dd in File 1, like they do in File 2?  Based on your snips, I would think that File 1 dates are not formatted as yyyy/mm/dd.  

If you want to fix this in Power Query, you can do so like this:

Table.AddColumn(#"Changed Type", "Custom", each try try Date.From ( [Column1] ) otherwise Date.From ( Number.From ( [Column1] ) ) otherwise [Column1] )

[RandomDenny]

That's a good observation. The date format actually is the same on both files (yyyy/mm/dd) but upon further investigation the cells are formated as "custom." If I reformat those cells as "General" or "Text" directly in Excel it changes the values in exactly the same way I'm seeing in the preview. So I assume that Power Query is somehow making a good interpretation of the data in those cells in the first file but then failing to do so in the subsequent files.

I can copy the data from that column out, change the cell formatting, and paste the data back in, and then make it all work from there... But that's a really messy manual intervention that I'd like to automate my way around if at all possible.

[jennratten]

The best option would be to have the files generated with the same formatting.  This would most likely be something that would apply to new files only.  If existing files are not regenerated, then they would need to be updated, as you mentioned, or you will need to include a fix for it in Power Query, such as what I have provided.

[ND_Pard]

Verify the Date columns are formatted the same, not just look the same.
Good Luck.