Forum Discussion
M Language on Date Formatting
Hi,
I have this column in Text data type. The year portion is in YYYY and some in YY and month portion is in m and some in mm
I found an online article that maybe useful to me problem. I mimicked the M language and put it in Power Query.
#"New Date" = Table.AddColumn(
#"Trimmed Text",
"tempDate",
each try
if Text.Contains([COMMENCEMENT DATE], "/") then
"20"
& Text.Split([COMMENCEMENT DATE], "/"){2}
& "-"
& Text.PadStart(Text.Split([COMMENCEMENT DATE], "/"){0}, 2, "0")
& "-"
& Text.PadStart(Text.Split([COMMENCEMENT DATE], "/"){1}, 2, "0")
else
Text.Range([COMMENCEMENT DATE], 0, 10)
Otherwise
null
in
#"New Date"
However, there is an error on the Otherwise part. I tried to increase and decrease the indentation still unable to solve it.
Kindly enlight me on the M language. Thanks.
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.
2 Replies
Thanks for the advise. 😀
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.
