YYYMMDD Conversion to MM/DD/YYYY in Power QUery

Question

I have a column named DimDateKey in PowerQuery and it is formatted as YYYYMMDD i.e. 20150201 and I want to convert it to MM/DD/YY and I have had no luck finding the syntax for this.

I have tried this which works in a regular excel sheet:

DATE(RIGHT([DimDateKey],4),MID([DimDateKey],4,2),LEFT([DimDateKey],2))

But I get

Expression error: The Name DATE was not recognized. Is it Spelled Correctly?

Answer 1

I figured this out

Step 1: Add a custom column Step 2: Format it as =Date.FromText([DimDateKey])

Answer 2

I would just change the Data Type to Date. The Data Type button is on the Home ribbon, in the Transform section, or you can right-click the column and choose Change Type.

Note the column needs to be Data Type: Text for this to work. Presumably due to an overly-rigid PQ evaluation engine, converting Data Type Number (containing the same YYYYMMDD value) returns an Error.