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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Removing Text from a date column

I have a row that is meant to store the date when an item was launched. It is currently stored as a mix of just the date in format "1/10/19" or in the format "Launched-1/10/19" I want to remove everything but the date itself so the column can be changed into date format. I have tried using Text.Remove in the formula for a new column but I get an error that Text.Remove is not a function. Any help would be appreciated.

 

Thanks!

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Try adding a custom column in the query editor with this formula.  It will only keep the numbers and / from your text.

 

= Text.Select([DateTextColumn], {"0".."9", "/"})

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Employee
Employee

Try adding a custom column in the query editor with this formula.  It will only keep the numbers and / from your text.

 

= Text.Select([DateTextColumn], {"0".."9", "/"})

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


az38
Community Champion
Community Champion

Hi @Anonymous 

Execute Split Column by Delimiter "-" in Power Query Editor mode (Transform ribbon)


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

I apologize I should have been more specific in my question, some are formatted "Launched-1/2/20" some are "Launched 1/2/20" some are "soft transition 1/2/20" and some are just "1/2/20". How would I get all of the dates in one column using split by delimiter.

Hey Nickpin,

 

    This might be a bit of a pain, but you might have to do a few delimiter splits and a custom column or two to get your data just right from the power query side. This is the play data I'm starting with

 

westwrightj_0-1594849463143.png

 

So what I'm going to do is actually split this by the common delimiter for the date which is "/"

 

 

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Dates", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Dates.1", "Dates.2", "Dates.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Dates.1", type text}, {"Dates.2", Int64.Type}, {"Dates.3", Int64.Type}})
in
    #"Changed Type1"

 

 

That will give us this

 

 

westwrightj_1-1594849533024.png

 

Next we are going to create a custom column that pulls the numerical value out of the first field and add it with the other fields

 

 

= Table.AddColumn(#"Split Column by Delimiter", "Date", each Text.Select([Dates.1], {"0".."9"}) 
&
"/"
&
[Dates.2]
&
"/"
&
[Dates.3])

 

 

 

Which will give us this result

 

 

westwrightj_2-1594849655616.png

 

Go ahead and remove your old columns and change the data type to date and you should be set. 

 

westwrightj_3-1594849693972.png

 

Here are the steps from the advanced editor

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kkszUvOSE3RNdQ30jcyUIrVQYgpIIkhMYPz00oUSooS84ozSzLz8+CqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dates = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Dates", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Dates.1", "Dates.2", "Dates.3"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Date", each Text.Select([Dates.1], {"0".."9"}) 
&
"/"
&
[Dates.2]
&
"/"
&
[Dates.3]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Dates.1", "Dates.2", "Dates.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}})
in
    #"Changed Type1"

 

 

 

 

 

 

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.