Split string by Delimiter but that Delimiter is used within substrings

[nckpedersen]

I'm working with an outside data source and someone there had the bright idea to concatenate their category lists with a comma but some of those categories include a comma that I can't split. Example below: 

Asset Management,Building Control Systems/Logic Controller,Data and Voice Lines,Data Collection Devices,Transit Control Center (TOC, TMC, TrMC, TCC, C3) Design, Integration, and Equipment),Fiber Optics

I need:

• Asset Management
• Building Control Systems/Logic Controller
• Data and Voice Lines
• Data Collection Devices
• Transit Control Center (TOC, TMC, TrMC, TCC, C3) Design, Integration, and Equipment) [Yes, this appears to be a spelling error without the opening parenthesis] 
• Fiber Optics

It does seem that those non-separable substrings are enclosed within parentheses but running a logic rule like search("(*,*)") will show even non comma separated parentheticals because they'll find the opening and go all the way until the next close, even if another ")" comes first.

I'm trying to avoid a "replace values" situation where I fix these upfront because I'm going to be pulling this automatically moving forward and don't know if new values will be added. 

Any ideas on how you would write the query? TIA!

[jjrobins14]

Hi There, i have a similar problem where my source data is using commas to seperate where multiple entires but a comma may also be used in the text string as part of the entry. I am trying to split this column into rows per entry.

As you can see

I have an entry under HCM-4.1.10 which as has an entry: 11.7.7 Talent Matching, Tracking and Personalization

I need to split these into lines where the split result has a line for each ignoring the use of comma between the strings.

HCM-4.1.10		11.7.5 Recruitment Lifecycle Management
HCM-4.1.10		11.7.7 Talent Matching, Tracking and Personalization

my initial thought would be that the split should occur if the spliting syntax is [comma][space][any digit] but my coding skills in dax arent that good yet.

any help would be amazing.

[Jakinta]

Here is another way to do it.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY7BCsIwEER/ZenJQsCDX6CpgtDSg8VL6SGmS1hoNzWbCv69SUUvc5idfTN9XxxFMEJj2DickaM6rTSNxA605xj8BLe3RJxlX3tH9udOGFRlogHDI9w9WYSaGOVr6hywkTxDha90FNUFw0LxT9WpCwPsulYr6JosYVOdRB/K9CjkWME15VwwGaa2tvNzpSVPLdWFHonRLpGsFMPwAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let t=Text.Split( [Column1], ","), f= List.FirstN(t, 4), l= List.Last(t), m= Text.Trim( Text.BetweenDelimiters( [Column1], List.Last(f),l), ",") in f & {m} & {l}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

[smpa01 · Accepted solution]

@nckpedersen  okay let's try this

let
fx=
let   fx=(input)=>
    Web.Page(
        "<script>
        var x='"&input&"'; 
        var b=x.replace(/,(?![^(]*\))/gm,'-'); 
        document.write(b);
        </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text]
in
fx,

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY7BCsIwEER/ZenJQsCDX6CpgtDSg8VL6SGmS1hoNzWbCv69SUUvc5idfTN9XxxFMEJj2DickaM6rTSNxA605xj8BLe3RJxlX3tH9udOGFRlogHDI9w9WYSaGOVr6hywkTxDha90FNUFw0LxT9WpCwPsulYr6JosYVOdRB/K9CjkWME15VwwGaa2tvNzpSVPLdWFHonRLpGsFMPwAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fx([Column1])),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}, {"Custom.4", type text}, {"Custom.5", type text}, {"Custom.6", type text}})
in
#"Changed Type1"

[smpa01]

@nckpedersen  did you try  the above ?