Split column in Excel's Power Query

Hi SlothCR ,

Please try this way:
Here is the whole M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZDLCgIxDEV/JXSlUL/CQRxEGBDcDF3UmpEwaQp9DPj31lm4cFXBVcKBe0/IOKoB4xSit+IQdtDhQnU5W56KuExBKuzFcUm0IOxtwlTJe8Kml4ys4TjommPeamgo62LxnuQBl1DkXsFJgpsrUEZ/X7NqDhTXMhvnj/9KiW7ElJ9/d/5W0KRv/Z8y5gU=", 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}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1.2", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type text}, {"Column1.2.2", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"Column1.1", "Column1.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Merged", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each if Text.Contains([Merged], "Inclusive Case") then "(" & [Column1.2.1] & ")" else ""),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Custom", "Merged.1", each Text.Combine({[Merged], [Custom]}, ""), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Column1.2.1", "Merged", "Custom"})
in
    #"Removed Columns"

And the final output is as below:

I'm assuming that the keyword Inclusive Cases is present in all of your lines containing (), so please feel free to modify it to your specific situation:

#"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each if Text.Contains([Merged], "Inclusive Case") then "(" & [Column1.2.1] & ")" else ""),

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

With this data (added a 4th row with even more splits):

I got this result:

Here is the M:

let
    Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45WCkgtSssvyk3MS05V0FVwSS3LBDJ8E3PSSvOSSzLz84CCnnnJOaXFmWWpCs6JxanFQBEQraDhmVeSmqOj4BGgA9SXk6Opo0CEYS5Fpbm5mXnpCsH5pXkpQAHvvPzkbKCAUqwOumvA1rhlFoENSyzKhtsfllmcmZSZk1lSSXU7STOAKOuJDb/B5Rr6G4YnmmIB", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ( (type nullable text) meta [ Serialized.Text = true ] ) in type table [ OriginalCol = _t ] ),
    ReplacerChar = "☃",
    ParseText = Table.AddColumn(
        Source,
        "Rows",
        each let
            leftSplit = Text.Split([OriginalCol], "("),
            replaceParenComma = List.Transform(
                leftSplit,
                each let
                    rightSplit = Text.Split(_, ")"),
                    replaceComma =
                        if List.Count(rightSplit) > 1 then
                            {Replacer.ReplaceText(rightSplit{0}, ",", ReplacerChar)} & {rightSplit{1}}
                        else
                            rightSplit,
                    comboRightParen = Text.Combine(replaceComma, ")")
                in
                    comboRightParen
            ),
            comboLeftParen = Text.Combine(replaceParenComma, "("),
            splitRows = List.Transform(
                Text.Split(comboLeftParen, ","),
                each Text.Split(Replacer.ReplaceText(_, ReplacerChar, ","), " - ")
            )
        in
            splitRows,
        type { { text } }
    ),
    ExpandRows = Table.ExpandListColumn(ParseText, "Rows"),
    ColumnSchema =
    let
        MaxFields = List.Max(List.Transform(ExpandRows[Rows], each List.Count(_))),
        colNames = List.Generate(() => 1, each _ <= MaxFields, each _ + 1, each "col" & Text.From(_)),
        types = List.Generate(
            () => 1, each _ <= MaxFields, each _ + 1, each [Optional = false, Type = type nullable text]
        )
    in
        Record.FromList(types, colNames),
    ConvertToRecords =
    let
        outputType = Type.ForRecord(ColumnSchema, false)
    in
        Table.TransformColumns(ExpandRows, {"Rows", each Record.FromList(_, outputType), outputType}),
    ExpandRecords = Table.ExpandRecordColumn(ConvertToRecords, "Rows", Record.FieldNames(ColumnSchema))
in
    ExpandRecords

High level explanation:

1) Split everything on (

2) Within the resulting list of split text, split again on )

3) Within your double-split text (in a list of a list of text), if there is more than 1 item, the first will be the text in your paren

4) replace your inside-paren-commas with a rare/source-impossible character (specify in ReplacerChar step)

5) combine all your text parts back up - now you are where you started, except your inside-paren-commas are gone and you can split on commas without interference

6) Split on the leftover commas, now you can bring your inside-paren-commas back

Note that the bulk of the work is done in the ParseText step. Expand the rows and then convert your list of split values into columns. Note that everything after ExpandRows is just OCD automation of column expansion, which you can take or leave depending on your scenario.