Forum Discussion
Remove Characters from String which are present elsewhere in the string
Hi All,
Looking to clean a date column, but only some of the values are affected by random characters being included, as such:
I'd have done a simple Text.Remove but as you can see, several month names include the letter "A"....
Thanks for your help!
Hi Donerandparmo1,
I would suggest instead of using text. remove, you should extract the date from the column by using
Date.FromText(
Text.Select([Start Date], {"0".."9","-","/"})
) otherwise null🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
5 Replies
Realised I could do LEFT(10), any other solutions?
Hi Donerandparmo1,
I would suggest instead of using text. remove, you should extract the date from the column by using
Date.FromText(
Text.Select([Start Date], {"0".."9","-","/"})
) otherwise null🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Great idea, mate!
You can’t rely on simply extracting numbers and a few special characters from the text, because the date portion itself may contain letters. If the length of the date portion of the text is consistent, you can use this formula instead:
let x = [Start Date] in try Date.From(x, "en-gb") otherwise Date.From(Text.Start(x, 9))Another great one, cheers mate!
