Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@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.
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
@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.
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |