How to replace any number occurence after a character?

[Yonas]

Hi I have a dataset like this

Id   URI

1   /co11.og.net/colt/ipn.in/local/Hal/Processed/2017-11-16/07-15-17.ss

2   /co11.osyu.net/co/cxp/local/users/kkk/D/2020/06/Active_2020_06_04.ss

I want to replace any number occurence after "local" by "#", My result set should be like this

Id   URI

1   /co11.og.net/colt/ipn.in/local/Hal/Processed/#-#-#/#-#-#.ss

2   /co11.osyu.net/co/cxp/local/users/kkk/D/#/#/Active_#_#_#.ss

I could not do replace values using substitute and Replace straight forward. Any help?

[mahoneypat · Accepted solution]

This can be done without Python in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcwxC8IwEAXgvxI6m9xd0HYWHOwmglMoQdIgIaEpvVb03xtLocN7vLvhM6ZqeyHE495W3cFUa1F5gMtEKr/U4Oey0wxhHFQYIGX3THAtuU3ZeWbfg0ZqJJGkGrCMk6RGMe+g3kH+LhsJ7jNu2sJ+YogxwqVYGgFrOLs5vL39nxZri8dV7H4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Id   URI" = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Id   URI] = "1   /co11.og.net/colt/ipn.in/local/Hal/Processed/2017-11-16/07-15-17.ss" or [Id   URI] = "2   /co11.osyu.net/co/cxp/local/users/kkk/D/2020/06/Active_2020_06_04.ss")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Id   URI", Splitter.SplitTextByEachDelimiter({"local"}, QuoteStyle.Csv, false), {"Id   URI.1", "Id   URI.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Id   URI.1", type text}, {"Id   URI.2", type text}}),
    ReplaceListOfLists = List.Zip({{"0".."9"}, List.Repeat({"#"},10)}),
    ReplaceNumbers = Table.TransformColumns(#"Changed Type",{{"Id   URI.2", each Text.Combine(List.ReplaceMatchingItems(Text.ToList(_), ReplaceListOfLists),"")}}),
    #"Changed Type1" = Table.TransformColumnTypes(ReplaceNumbers,{{"Id   URI.2", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Id   URI.1", "Id   URI.2"},Combiner.CombineTextByDelimiter("local", QuoteStyle.None),"NewURI"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns","####","#",Replacer.ReplaceText,{"NewURI"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","###","#",Replacer.ReplaceText,{"NewURI"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","##","#",Replacer.ReplaceText,{"NewURI"})
in
    #"Replaced Value2"

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

Regards,

Pat

[Anonymous]

@mahoneypat Lots of kudo's for this solution. Helped me tremendously and I learned a few cool new M-functions!

[v-juanli-msft]

Hi @Yonas 

Paste the query in Advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcyxCsMgFIXhd3GO3nslNXOhQ8fuEqRYKaJoyDWlffvYEuhw4D/LZ60gMQjwlUjVpyqh9c4N4lJULJCrv2e49t3W6gNzeIBGmiSRJAPY4yRpUsxiHqzQf4s/26GBfy8HtHFYGVJKcOmMRkADZ9/iK7jvdWgcjj9s3gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, URI = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"URI", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "URI", Splitter.SplitTextByEachDelimiter({"local/"}, QuoteStyle.Csv, false), {"URI.1", "URI.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"URI.1", type text}, {"URI.2", type text}}),
    #"Run Python script" = Python.Execute("# 'dataset' holds the input data for this script#(lf)import pandas as pd#(lf)import re#(lf)dataset[""URI.2""]=dataset[""URI.2""].apply(lambda x: re.sub(r'[0-9][0-9a-z]+',r'#',x))#(lf)#(lf)",[dataset=#"Changed Type1"]),
    #"Expanded Value" = Table.ExpandTableColumn(#"Run Python script", "Value", {"Id", "URI.1", "URI.2"}, {"Value.Id", "Value.URI.1", "Value.URI.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded Value",{"Value.URI.1", "Value.URI.2"},Combiner.CombineTextByDelimiter("local", QuoteStyle.None),"URL")
in
    #"Merged Columns"

Python script

import pandas as pd
import re
dataset["URI.2"]=dataset["URI.2"].apply(lambda x: re.sub(r'[0-9][0-9a-z]+',r'#',x))

Best Regards

Maggie

[Yonas]

Thanks @v-juanli-msft for the solution, It looks like these might solve my problem but when i execute the python script, I get the below error

I am new to python, any ideas?

[v-juanli-msft]

Hi @Yonas 

Sorry for providing a solution using other tool.

Please check if amitchandak's suggestion could help.

To use Python script in Power BI, we need to install python in our machine and configure it in power bi setting.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts

Best Regards

Maggie

[v-juanli-msft]

Hi @Yonas 

In Power query, Split columns->run python scripts->expand columns->merge columns

please download my pbix and see details.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Yonas , Image not opening

refer

https://www.youtube.com/watch?v=UZQ9EFvEECY

https://docs.microsoft.com/en-us/dax/replace-function-dax

https://docs.microsoft.com/en-us/dax/substitute-function-dax

[Yonas]

The URI is not a hyperlink, it is just a record and no need to open it. I have checked the functions but as you can see from my scenario, the URI column is not consistent in terms of length and structure to apply those functions, the logic i want to apply to my records is " Replace any number after "Local" by this # " , so that my result will be like this

Id   URI

1   /co11.og.net/colt/ipn.in/local/Hal/Processed/#-#-#/#-#-#.ss

2   /co11.osyu.net/co/cxp/local/users/kkk/D/#/#/Active_#_#_#.ss

but i do not know how to achieve that.