How to replicate a Excel text-to-column date conversion

[maracles]

I export data from one of our services and the dates come out in mm/dd/yyyy format.  In Excel 2016 I can convert this very easily to dd/mm/yyyy in place without having to create any new columns. This is done by:

1. Text to columns
2. Uncheck delimiters
3. Choose 'Dates' and choose your appropriate format, see below:




 How would I replicate this in Power BI? At the moment I'm having to do the conversion in Excel before I import which is frustrating. I could somehow split columns at the "/" and then recombine but this seems too much work.  Any simpler suggestions?

Thanks.

[Greg_Deckler]

In your data model, click on the column. Click on "Modeling" tab. Under data type, click on "Format" drop down and choose the format that you want.

[Sean]

@maracles look at my last response with the 4 pictures here

http://community.powerbi.com/t5/Desktop/How-to-change-the-date-format/m-p/40462#M15202

[maracles]

Hi @Sean thanks for this, I had thought about doing it this way just figured there might be a simpler way! I'll use this method for now until they improve the basic functionality. Much appreciated. 

[radpir · Accepted solution]

there's a simpler way to do it via the PQ GUI:
right-click the column and then go Change Type => Using Locale...
then in the Dialog box select Date for Data Type and English (United States) for Locale

this should do the trick 🙂

[maracles]

@radpir Thank you for this, from your answer I managed to find a solution. I actually had to add a step which was to convert my column to text format first, and then use Change Type > Using Locale but after that it worked perfectly. 

@Greg_Deckler Thank you for your suggestion, that would also have worked however @radpir solution allows me to do it earlier in the ETL process.

Thank you both.