extract every occurrence of the text between two delimeters

[burnzy007]

I have an array column that Power BI reads as a string. An example of a record in this column is below:

"[{""cladding_type"":""Cement Fibre"",""ACM_ind"":false,""cladding_manufacturer"":""Celotex"",""insulation_type"":""Other"",""insulation_manufacturer"":""Other""},{""cladding_type"":""Metal composite panel - Unknown"",""ACM_ind"":false,""cladding_manufacturer"":""Valcan (aluminium / fibre cement)"",""insulation_type"":""Rigid (thermoset) foam - Polyisocyanurate (PIR)"",""insulation_manufacturer"":""Celotex""},{""cladding_type"":""Other"",""ACM_ind"":false,""cladding_manufacturer"":""Celotex"",""insulation_type"":""Rigid (thermoset) foam - Polyisocyanurate (PIR)"",""insulation_manufacturer"":""Recticel""}]"

I would like to extract every cladding type here. Specifically the text between every occurence of the following delimiters: """cladding_type"":""", """ and ""ACM_ind"":"

The output from the above should be something like:

Cement Fibre, Metal composite panel - Unknown, Other

[ThxAlot]

Obviously, it's a JSON string,

let
    Source = "[{""cladding_type"":""Cement Fibre"",""ACM_ind"":false,""cladding_manufacturer"":""Celotex"",""insulation_type"":""Other"",""insulation_manufacturer"":""Other""},{""cladding_type"":""Metal composite panel - Unknown"",""ACM_ind"":false,""cladding_manufacturer"":""Valcan (aluminium / fibre cement)"",""insulation_type"":""Rigid (thermoset) foam - Polyisocyanurate (PIR)"",""insulation_manufacturer"":""Celotex""},{""cladding_type"":""Other"",""ACM_ind"":false,""cladding_manufacturer"":""Celotex"",""insulation_type"":""Rigid (thermoset) foam - Polyisocyanurate (PIR)"",""insulation_manufacturer"":""Recticel""}]",
    #"Parsed JSON" = Json.Document(Source),
    #"Extracted cladding_type" = Table.FromRecords(#"Parsed JSON")[cladding_type]
in
    #"Extracted cladding_type"

[spinfuzer]

Reformat your text so it is in JSON format and then you can use Json.Document([column name]), expand, and filter.

Replace Value "[ with [

Replace Value "] with ]

Replace value "" with "

Then add custom column with formula =Json.Document([Column]).

[burnzy007]

Hi spinfuzer, thank you so much for replying promptly. Looks like this works mostly, but I would instead like each cladding type to appear in one row. in other words where there are three rows this should only be 1 row with each cladding type shown either in seperate columns or a merged column. 

[spinfuzer]

Extract to values instead of new rows.  However, you will get an error and you need to edit the formula to extract the cladding_type column of each record

= Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, (x) => Text.From(x[cladding_type])), ","), type text})

[burnzy007]

Hi Spinfuzer, this looks like the exact result I need. If you don't mind would you be able to share the m code in the screenshot please? Will make my life a lot easier. I will obviously change the source. Thanks in advance.

[spinfuzer · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","""[","[",Replacer.ReplaceText,{"col1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]""","]",Replacer.ReplaceText,{"col1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","""""","""",Replacer.ReplaceText,{"col1"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value2", "Custom", each Json.Document([col1])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, (x) => Text.From(x[cladding_type])), ","), type text})
in
    #"Extracted Values"

[burnzy007]

Thank you so much. So nearly there, but I get the error message below (FYI I renamed the column in my table to col1 to match your code). The data source is a databricks table.

[spinfuzer]

Your #"Changed Type" step is referencing Source instead of #"Renamed Columns" (the step before).

[burnzy007]

this is perfect, so many thanks!!! Lifesaver