Forum Discussion
replace a value within text but only between square brackets
Hi I've got some data as follows (in one column):
"third_party_funded": "Yes", "risk_score": 5.4, "tender_assurance_rating": "Amber/Green", "project_number": ["8675309", "771595", "107140"],...
It's comma delimited and in a later step I will be splitting each key value pair into columns however as you can see, there is a list in one of the KV pairs (Project Number), I would like to replace the commas in between the square brackets so that in my later step, they are not split.
Anonymous
Please add the following custom column where I replace the "," with "|" within the [...]let T=Text.BetweenDelimiters(_[Data], "[", "]") in Text.Replace( _[Data],T, Text.Replace(T,",","|" ))
Orginal Text:|
Result:
Full Code:
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcxBDsIgFATQqzR/bRRiEdudK89ggBCEr6KRNh9YeHtL2E1mXkYpKK9Iwa6Oys8+agoY5kHDDbOG3RYo5o/NfiHUMA9iP7ay4ObIupwrueTRkisxPZvQcPnekQ5XQkz9YqXljb7YVNvSkNJwPklxZFMXUnIxiZ45k3xkGgwY8wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let T=Text.BetweenDelimiters(_[Data], "[", "]") in Text.Replace( _[Data],T, Text.Replace(T,",","|" ))) in #"Added Custom"
2 Replies
Anonymous
Please add the following custom column where I replace the "," with "|" within the [...]let T=Text.BetweenDelimiters(_[Data], "[", "]") in Text.Replace( _[Data],T, Text.Replace(T,",","|" ))
Orginal Text:|
Result:
Full Code:
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcxBDsIgFATQqzR/bRRiEdudK89ggBCEr6KRNh9YeHtL2E1mXkYpKK9Iwa6Oys8+agoY5kHDDbOG3RYo5o/NfiHUMA9iP7ay4ObIupwrueTRkisxPZvQcPnekQ5XQkz9YqXljb7YVNvSkNJwPklxZFMXUnIxiZ45k3xkGgwY8wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let T=Text.BetweenDelimiters(_[Data], "[", "]") in Text.Replace( _[Data],T, Text.Replace(T,",","|" ))) in #"Added Custom"