replace a value within text but only between square brackets

[Anonymous]

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.

[Fowmy · Accepted solution]

@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"

[Anonymous]

You absolute legend!

Thanks that worked perfectly!