Excel Power Query date format messup

microsoft excelmicrosoft-excel-2016power-query

I'm trying to import dates from a csv file to Excel. The input is in the format of m/d/yyyy, and the same is my system setting:

System settings

However, when I change the Data Type setting upon importing, it messes the dates up. Apparently it switches to d/m/yyyy and then doesn't understand the dates:

Error

I have no idea how to change this. Even if I manually change the cell format to date with m/d/yyyy, it doesn't seem to have an effect. How can I make sure my dates get recognised properly with the column type change option without rewriting my input?

Thank you!

Best Answer

I'm not sure about this, but I think Power Query is recognizing the date per your locale, and not per the short date settings. I suggest one of these two solutions:

  1. In Power Query, when you change the Date Type setting, do this by right clicking on the column header. Then select Change Type --> Using locale. You should then have the opportunity to select English(US). or
  2. Use the Legacy Wizard which should give you the opportunity to specify the source date format at the time of import.
Related Question