Get multiple substrings and create a new row

Hi AndreasAh ,

My Sample:

Here I suggest you to add a special delimiter that not contained in your orginal table like “+” before "Y".

Then Split Column by Delimiter"+" by rows.

Then add a custom column to check whether your row contains "-"

Filter "True" in Custom column> Replace " "(space) by nothing. Spliter by position 7 by rows.

Finally remove some columns you don't need. Result is as below.

Whole M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY6xCsIwGIRf5cjchP5/FHHUzcXJpZQOoYltqW0giVjf3lCLi9txx313dS1IFOLWO3TBvXH3C9rgY3QWKZvBGwuTMA12Hro+KVwSXibCmjDCzBatf9gCFR3ljikLJlmWvEYVs9R0ULi6Ja04+2zH+DegRFPUgvOPE4b5W2OCDxuBFc6/QOuNrSXpfa42Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report ID" = _t, Substring = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report ID", Int64.Type}, {"Substring", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Y","+Y",Replacer.ReplaceText,{"Substring"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Substring", Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Substring"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Substring", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Contains([Substring],"-")),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows"," ","",Replacer.ReplaceText,{"Substring"}),
    #"Split Column by Position" = Table.SplitColumn(#"Replaced Value1", "Substring", Splitter.SplitTextByRepeatedLengths(7), {"Substring.1", "Substring.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Substring.1", type text}, {"Substring.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Substring.2", "Custom"})
in
    #"Removed Columns"

Best Regards,
Rico Zhou

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