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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Remove Text between a pattern of string

I have a Column with data as below

"/Data/Bucket-Folder/Heritage-Bucket-Folder/2019/09/02/01/30/newtext"

Here i would like remove the pattern of text starting with numbers/dates as following 

2019/09/02/01/30/

The above number/date can change.

 

The final output to be:

"/Data/Bucket-Folder/Heritage-Bucket-Folder/newtext"

How can I do? I tried with the following 

Table.ReplaceValue(#"Removed Top Rows","/2019/09/02/01","",Replacer.ReplaceText,{"Url"})

This replaced only "2019/09/02/01/" Not the following number. These numbers/date change in the string. So cannot use the hardcoded values to replace.

1 ACCEPTED SOLUTION

@Anonymous 

Have you tried this custom column

Text.Combine(List.Select(Text.Split([Column1],"/"),each Text.Start(_,1) > "A"),"/")

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

 

View solution in original post

9 REPLIES 9
AnkitBI
Solution Sage
Solution Sage

Below is how I am able to achieve it. #"Changed Type"{0}[Column1] is to get the Text Value. You may need to modify as per your data.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilHSd0ksSdR3Kk3OTi3RdcvPSUkt0vdILcosSUxP1UUVNjIwtNQ3ACIjfQNDfWMD/bzU8pLUipIYJaXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Custom1 = Text.Combine(List.Select(Text.Split(#"Changed Type"{0}[Column1],"/"),each Text.Start(_,1) > "A" or Text.Start(_,1) = """"),"/")
in
    Custom1

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

Try this custom column

 

=Text.Start([ColumnName],Text.PositionOf([ColumnName],"/2"))
&
Text.End([ColumnName],Text.Length([ColumnName])-Text.PositionOf([ColumnName],"/",Occurrence.Last))

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hi, Thanks for the solution. I think it is my bad questioning that I missed out to let you know that their are some records without this format. So how to add a condition to skip those that do not fall under this format... Being a novice


@Zubair_Muhammad wrote:

@Anonymous 

Try this custom column

 

=Text.Start([ColumnName],Text.PositionOf([ColumnName],"/2"))
&
Text.End([ColumnName],Text.Length([ColumnName])-Text.PositionOf([ColumnName],"/",Occurrence.Last))

 

Hi @Anonymous 

 

Please could you copy paste some sample rows with expected output


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hi,
Please find the table data as below:

"/situations/i-need-to-file-my-employment-information"
"/Data/Bucket-Folder/Heritage-Bucket-Folder/2019/09/02/01/37/Cancelled-resident-withholding-tax-exemp "

 should be changed as below

/situations/i-need-to-file-my-employment-information

/Data/Bucket-Folder/Heritage-Bucket-Folder/Cancelled-resident-withholding-tax-exemp


the row which doesn't start with "/Data/Bucket-Folder/Heritage-Bucket-Folder/" should be processed that the date following this text should be removed. If the row doesnt start with this text, then it should not do anything.


Hope this data helps.

Anonymous
Not applicable

@Zubair_Muhammad, i tried with the following:

This creates a lot of duplicates after expanding the custom column.

 

Table.AddColumn(#"Removed Top Rows", "Custom", each if Text.StartsWith([Url], "/Data/Bucket-Folder/Heritage-Bucket-Folder/") then (Text.Start([Url],Text.PositionOf([Url],"/2"))
        & Text.End([Url],Text.Length([Url])-Text.PositionOf([Url],"/",Occurrence.Last)))
        else [Url])

 

@Anonymous 

Have you tried this custom column

Text.Combine(List.Select(Text.Split([Column1],"/"),each Text.Start(_,1) > "A"),"/")

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

 

z3055154911384_c9b288eebc70456a8a958c6bba846b78.jpg

 So I have this type of data. Could you help me write the formula that will return the values as "-00_김미자35-a3-35" for example.
I want to remove the date value. Thanks

I am new to power query. Could you please explain this formular? This is what I am looking for

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors