Separating Values Into Rows

[BO7]

I have a column that contains multiple ID's. The format of each cell is different; for example, I have a cell that looks like this:

[APP1234567APP9876543APP6789012]

I also have cells that look like this:

[APP1234567879;APP245674128;APP5558246]

I also have cells that have a combination of the above datasets. Is there a way to separate the data so that each value that starts with 'APP' has its own row? The delimiter function is not helping me with this.

[SundarRaj · Accepted solution]

Hi @BO7 , here's another solution you could look. Thanks!

Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Texts", type text}}),
Number = Table.TransformColumns(#"Changed Type",{}, each List.Count(Text.PositionOf(_,"A",Occurrence.All)) + 1),
ColNumber = List.Max(Number[Texts]),
ColNames = List.Transform({1..ColNumber}, each "Col" & Text.From(_)),
Custom1 = #"Changed Type",
#"Split Column by Delimiter" = Table.SplitColumn(Custom1, "Texts", Splitter.SplitTextByDelimiter("APP", QuoteStyle.Csv), ColNames),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Col1"}),
Custom2 = Table.TransformColumns(#"Removed Columns",{}, each Text.Replace("APP" & _,";","")),
Custom3 = Table.FromList(List.Combine(Table.ToRows(Custom2)))
in
Custom3

[BO7]

Thank you! I was pulling my hair out trying to figure this one out.

[jgeddes]

You can create a list for each row using Text.Split() and "APP" as the value to split on.
Remove any blank rows from the resulting list and then remove any non-number characters. 
Add "APP" back to the start of each row of the list.
Delete the original column.

Expand the lists into rows.

Example code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwIMDQyNjE1MweyLC3MzUxNjIEsM3MLSwNDI6VYHWQlFuaW1kCeEYhtYmhkAeKYmppaGJmYKcXGAgA=", 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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform(List.RemoveItems(Text.Split([Column1], "APP"), {""}), each "APP" & Text.Select(_, {"1".."9"})), type list),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom")
in
    #"Expanded Custom"