Split column in Excel's Power Query

[SlothCR]

Hi,

In Excel I have a column with this info (earch bullet is a row with a whole lot more columns left and right):

1. Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Drumming Sound - Knocking
2. Performance - Case Fire - Dark Cases - Visibility, Performance - Device Malfunction - Drumming Sound - Knocking
3. Performance - Device Malfunction - Drumming Sound - Knocking, Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell)

I need to be able to split this column in Excel's Power Query on every single comma OUTSIDE a parenthesis, so it can return this:

• Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell)
• Performance - Device Malfunction - Drumming Sound - Knocking
• Performance - Case Fire - Dark Cases - Visibility
• Performance - Device Malfunction - Drumming Sound - Knocking
• Performance - Device Malfunction - Drumming Sound - Knocking
• Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell)

For example: bullet a has 2 instances separated by a comma outside a parethesis. When split, I will have now 2 columns, each will have 1 of those instances. I'm not sure how to create a CUSTOM COLUMN that will make this very specific split.

Thanks for any assistance you can provide

[v-junyant-msft · Accepted solution]

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.

[MarkLaf · Accepted solution]

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

OriginalCol

Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Drumming Sound - Knocking

Performance - Case Fire - Dark Cases - Visibility, Performance - Device Malfunction - Drumming Sound - Knocking

Performance - Device Malfunction - Drumming Sound - Knocking, Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell)

Performance - Device Malfunction - Drumming Sound - Knocking, Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Drumming Sound - Knocking

I got this result:

OriginalCol	col1	col2	col3	col4
Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Drumming Sound - Knocking	Performance	Device Malfunction	Inclusive Cases	Case (Intel, HP, Dell)
Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Drumming Sound - Knocking	Performance	Device Malfunction	Drumming Sound	Knocking
Performance - Case Fire - Dark Cases - Visibility, Performance - Device Malfunction - Drumming Sound - Knocking	Performance	Case Fire	Dark Cases	Visibility
Performance - Case Fire - Dark Cases - Visibility, Performance - Device Malfunction - Drumming Sound - Knocking	Performance	Device Malfunction	Drumming Sound	Knocking
Performance - Device Malfunction - Drumming Sound - Knocking, Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell)	Performance	Device Malfunction	Drumming Sound	Knocking
Performance - Device Malfunction - Drumming Sound - Knocking, Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell)	Performance	Device Malfunction	Inclusive Cases	Case (Intel, HP, Dell)
Performance - Device Malfunction - Drumming Sound - Knocking, Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Drumming Sound - Knocking	Performance	Device Malfunction	Drumming Sound	Knocking
Performance - Device Malfunction - Drumming Sound - Knocking, Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Drumming Sound - Knocking	Performance	Device Malfunction	Inclusive Cases	Case (Intel, HP, Dell)
Performance - Device Malfunction - Drumming Sound - Knocking, Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Drumming Sound - Knocking	Performance	Device Malfunction	Inclusive Cases	Case (Intel, HP, Dell)
Performance - Device Malfunction - Drumming Sound - Knocking, Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Inclusive Cases - Case (Intel, HP, Dell), Performance - Device Malfunction - Drumming Sound - Knocking	Performance	Device Malfunction	Drumming Sound	Knocking

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.

[v-junyant-msft · Accepted solution]

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.