Split column into rows if cell does not start with "A-" or "B-".

[max_mustermann]

I want to split a column into rows according to delimiter if the text doesn't start with "A-" or "B-".

Here is my original column1:

column1_original

A-2389

B-23893a

A-kljasdf

89324-234

abc-43892

And now I want to split all values except the ones who start with "A-" or "B-" into new rows according example:

column1_original	column1_new
A-2389	A-2389
B-23893a	B-23893a
A-kljasdf	A-kljasdf
89324-234	89324
89324-234	234
abc-43892	abc
abc-43892	43892

So in pseudocode it would be somethin like:

IF CELL starts with "A-" OR "B-"

      nothing

Else
      Split according delimiter "-" into new rows

[AlexisOlson · Accepted solution]

Define a new custom column with the following logic:

if Text.StartsWith([column1], "A-") or Text.StartsWith([column1], "B-")
then {[column1]}
else Text.Split([column1], "-")

Then expand that column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctQ1MrawVIrViVZyAjONE8EcR93snKzE4pQ0MA8obGQClDYB8xKTknVNgEqNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "new", each if Text.StartsWith([column1], "A-") or Text.StartsWith([column1], "B-") then {[column1]} else Text.Split([column1], "-"), type list),
    #"Expanded new" = Table.ExpandListColumn(#"Added Custom", "new")
in
    #"Expanded new"

[wdx223_Daniel]

NewStep=#table(2,List.TransformMany(Table.ToRows(PreviousStepName),each if List.Contains({"A-","B-"},_,(x,y)=>Text.StartsWith(y,x)) then {_} else Text.Split(_,"-"),(x,y)=>{x,y}))

[max_mustermann]

Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctQ1MrawVIrViVZyAjONE8EcR93snKzE4pQ0MA8obGQClDYB8xKTknVNgEqNlGJjAQ==", 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}}),
    #"NewStep" = #table(2,List.TransformMany(Table.ToRows(#"Changed Type"),each if List.Contains({"A-","B-"},_,(x,y)=>Text.StartsWith(y,x)) then {_} else Text.Split(_,"-"),(x,y)=>{x,y}))
in
    #"NewStep"