Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alvin199
Helper III
Helper III

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

alvin199_0-1644032182810.png

 

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. 

 

alvin199_1-1644032355602.png

 

Kindly enlight me on the M language. Thanks.

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@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.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

2 REPLIES 2
alvin199
Helper III
Helper III

@AllisonKennedy 

Thanks for the advise. 😀

AllisonKennedy
Super User
Super User

@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.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.