Forum Discussion
Anonymous
3 years agoNot applicable
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"],... ...
- 3 years ago
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"
Fowmy
3 years agoSuper User
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"
- Anonymous3 years agoNot applicable
You absolute legend!
Thanks that worked perfectly!