Split Column Based on String Text

[beauschoen]

Hello! I have a column containing trading card series, card number and card name that I want to split, such as:

• "1989 Comic Images Excalibur 1 Checklist" split into "1989 Comic Images Excalibur" and "1 Checklist
• "1990 Comic Images Uncanny X-Men II 20 Issue #195"  split into "1990 Comic Images Uncanny X-Men II" and  "20 Issue #195"

There isn't consistency in the data to split by Characters or Delimiters, that I have found.

Q: Is it possible to specify that if the string contains certain data, to split a certain way? Something like:

IF [Card] contains "1989 Comic" then split....., IF [CARD] contains "1990" then split....

I've tried modifying the code after doing one split, but cannot make Power Query happy.

[Anonymous]

Hi, @beauschoen 

Optimize with M code. For more complex logic that can't be implemented through the UI, consider using the Advanced Editor to manually write or tweak M-code. For example, I create a table

Then use the following M expression

let
CustomFunction = (text as text) as table =>
    let
        splitText = if Text.Contains(text, "1 Checklist") then 
                        Text.Split(text, "1989 Comic Images Excalibur")
                    else if Text.Contains(text, "20 Issue #195") then 
                        Text.Split(text, "1990 Comic Images Uncanny X-Men II")
                    else 
                        {text},
        toTable = Table.FromList(splitText, Splitter.SplitByNothing())
    in
        toTable,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrS0sFRwzs/NTFbwzE1MTy1WcK1ITszJTCotUjBUcM5ITc7OySwuUYrVAam1NEBVG5qXnJiXV6kQoeubmqfg6algZKDgWVxcmqqgbGhpqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column = _t]),
      #"Invoked Custom Function" = Table.AddColumn(Source, "Split", each CustomFunction([column])),
    #"Expanded Split" = Table.ExpandTableColumn(#"Invoked Custom Function", "Split", {"Column1"}, {"Split.Column1"})
in
    #"Expanded Split"

Here is my preview:

This is just an idea of mine, and you can modify it based on your data

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[jgeddes]

You might want to explore the List.Contains function. 
M - List.Contains