Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I want to convert this text column to a date column and add the day name to the date.
Solved! Go to Solution.
Hi @Aha2022
You can first add a custom column to get the date values without "st/nd/rd/th" into a new column.
Text.RemoveRange([Date], Text.PositionOf([Date], " ")-2, 2)
Then change the new column to Date type. With the new format, dates can be converted into Date type successfully.
If you want to change the format of dates, you need to apply the data to Power BI Desktop and change the column format there, just like below image. In Power Query Editor, it doesn't support to change the date format when the column is of Date type.
If you don't want to add a custom column to get the new date values, you can transform the original column with the following M code. Then modify the column type to Date and apply it to Power BI Desktop to change the date format.
= Table.TransformColumns(#"Changed Type", {{"Date", each Text.RemoveRange(_, Text.PositionOf(_, " ")-2, 2)}})
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Function for Power query that converts a text with dates (like 11/5/2023 or 4.5.22 ) to dates based on a kwon format (MM.DD.YYYY).
(Creating a function from an existing endpoint is a simple process. Right-click on the endpoint name and choose Create Function.)
Over the new function, in advace editor past the code:
let |
Then invoke the function in a new column in power query.
Hi @Aha2022
You can first add a custom column to get the date values without "st/nd/rd/th" into a new column.
Text.RemoveRange([Date], Text.PositionOf([Date], " ")-2, 2)
Then change the new column to Date type. With the new format, dates can be converted into Date type successfully.
If you want to change the format of dates, you need to apply the data to Power BI Desktop and change the column format there, just like below image. In Power Query Editor, it doesn't support to change the date format when the column is of Date type.
If you don't want to add a custom column to get the new date values, you can transform the original column with the following M code. Then modify the column type to Date and apply it to Power BI Desktop to change the date format.
= Table.TransformColumns(#"Changed Type", {{"Date", each Text.RemoveRange(_, Text.PositionOf(_, " ")-2, 2)}})
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @Aha2022
If you can remove unnecessary characters like th, Rd, st and ",' you can conver it to date it is best to do the cleaning and treating date field as date type then you can perform all the date functions and time intelligence in DAX.
If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
41 | |
23 | |
21 | |
21 | |
13 |