M Language on Date Formatting

alvin199  If you're not confident with M language, you can use the buttons in the ribbon to achieve what you need.

Select your column > Transform tab > Split Column > By Delimiter

Type / as your delimiter

You'll now have a column for Month, Day, Year. Rename them to make things easier.

Select the Month column. 

Click Transform > Format > Add Prefix

Type 0 as your prefix.

Select the Month column still.

Click Transform > Extract > Number of Characters.

Type 2 and starting from right.

Select the Year column.

Click Transform > Extract > Number of Characters.

Type 2 and starting from right.

Select the Year column still.

Click Transform > Format > Add Prefix

Type 20 as your prefix.

Then use the Ctrl key to select the Month, Day, Year columns in order.

Click Transform > Merge.

Type / as the delimiter (choose custom)

Change Data type for the new column to Date and rename it.

I know this feels like a lot more steps than the one custom column, but in the end it's probably not that much less efficient and is far easier to understand when you don't know M. 🙂 

Let me know if you get stuck on any part.