Forum Discussion
Cell values changing when files are combined
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.
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.