Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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.
@Anonymous
Try this custom column
=Text.Start([ColumnName],Text.PositionOf([ColumnName],"/2")) & Text.End([ColumnName],Text.Length([ColumnName])-Text.PositionOf([ColumnName],"/",Occurrence.Last))
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
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.
@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.
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