Force date locale change from UK to US from a US locale spreadsheet RRS feed

  • Question

  • I am trying to convert a date in Power Query that is in the more traditional date format of DD/MM/YYYY to MM/DD/YYYY. This is easy to do with a CSV file, but the source here is a spreadsheet that is generated as a US Locale format. Our bank in Hong Kong does this for some reason and cannot change it.

    So the date comes down as DD/MM/YYYY and using the Change Type using Date and Locale does nothing. I can export the file to CSV and bring it in that way and it works fine.

    If using the Excel file though, is my only recourse to deconstruct the date components and reassemble them in the MM/DD/YYYY format?

    Sunday, January 14, 2018 3:38 PM


  • If you right click on the source column in Power Query, there is a me u item that will do this. I don’t remember exactly, but it is something like Convert using Locale, then specify the source locale.

    Self Service BI Expert using Power Pivot

    Monday, January 15, 2018 8:08 AM