Convert Text to Date in PowerPivot

[brent_milne]

I am importing data using an ODBC connection and one of the columns is a date formatted as text as dd/mm/yyyy (Canadian). I need to be able to convert this to a date formatted as mm/dd/yyyy in my PowerPivot table. I was hoping to be able to do this with a new calculated column so I can use it as a slicer in a pivot table as well as with other date formulas. I have searched all over but can't seem to find what I am looking for so I hope somebody can start me with a formula that may help. Thanks for any help.

[teylyn]

Hello,

it might be easiest to use Power Query to get the data, clean it up and then load it straight to the data model. That's what Power Query is good at and what it's made for.

Split the text column by the custom delimiter "/", in a new column, concatenate the resulting three columns in the way you need, then change the data type of the new column to date. Finally delete the three columns created by the split.

cheers, teylyn

[brent_milne]

thanks....i ended up splitting the date into 3 new columns then concatenating them into a new column in the order I needed then could format as a date.