Forum Discussion

Donerandparmo1's avatar
Donerandparmo1
Icon for Helper I rankHelper I
5 months ago
Solved

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,...
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    5 months ago

    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!

danextian's avatar
danextian
Icon for Super User rankSuper User
5 months ago

Hi Donerandparmo1 

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))

 

Donerandparmo1's avatar
Donerandparmo1
Icon for Helper I rankHelper I
5 months ago

Another great one, cheers mate!