Forum Discussion

BO7's avatar
BO7
Regular Visitor
1 year ago
Solved

Separating Values Into Rows

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.

Need Help
Tips and Tricks
  • SundarRaj's avatar
    SundarRaj
    1 year ago

    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

     

     

3 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    1 year ago

    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"

     

     

  • SundarRaj's avatar
    SundarRaj
    Super User
    1 year ago

    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's avatar
      BO7
      Regular Visitor
      1 year ago

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