Forum Discussion

Mederic's avatar
Mederic
Post Patron
6 months ago
Solved

Split a column into two separate columns

Hello,

I have a data range of approximately 2,000 rows, each containing the supplier number followed by the name.

The table is structured using this same logic throughout.

I would like to transform this column into two columns with the number and the name.

I have two methods that work, but I would like to optimise them with fewer steps.

If possible, I would also like to see one or two other methods suggested.

I am attaching a small sample file.

Thanks in advance

Best regards

Need Help
  • AlienSx's avatar
    AlienSx
    6 months ago
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    group = Table.Group(
        Source, 
        "List", 
        {"fx", (x) => Record.FromTable(Table.SplitColumn(x, "List", Splitter.SplitTextByEachDelimiter({" "}), {"Name", "Value"}))}, 
        GroupKind.Local, 
        (s, c) => Number.From(Text.StartsWith(c, "Supplier"))
    ),
    z = Table.FromRecords(group[fx])
in
    z

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    result = Table.FromList(
        List.Split(Table.ToList(Source, (x) => Text.AfterDelimiter(x{0}, " ")), 2),
        (x) => x, 
        {"Supplier", "Name"}
    )
in
    result

     

3 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    6 months ago
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    group = Table.Group(
        Source, 
        "List", 
        {"fx", (x) => Record.FromTable(Table.SplitColumn(x, "List", Splitter.SplitTextByEachDelimiter({" "}), {"Name", "Value"}))}, 
        GroupKind.Local, 
        (s, c) => Number.From(Text.StartsWith(c, "Supplier"))
    ),
    z = Table.FromRecords(group[fx])
in
    z

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    result = Table.FromList(
        List.Split(Table.ToList(Source, (x) => Text.AfterDelimiter(x{0}, " ")), 2),
        (x) => x, 
        {"Supplier", "Name"}
    )
in
    result

     

  • OwenAuger's avatar
    OwenAuger
    Super User
    6 months ago

    Hi Mederic 

    Here's one suggestion:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type text}}),
    #"Split List" = List.Split(#"Changed Type"[List],2),
    #"Extract Text" = List.Transform(#"Split List", each { Text.AfterDelimiter(_{0},"Supplier "), Text.AfterDelimiter(_{1},"Name ") } ),
    #"Convert to Table" = Table.FromList(#"Extract Text", each _, type table[Supplier = text, Name = text])
in
    #"Convert to Table"

     List.Split is used to partition the list into pairs.

    Depending on the source, there might be some need to ensure original row order is preserved when the list is split.

  • Mederic's avatar
    Mederic
    Post Patron
    6 months ago

    Hello OwenAuger , AlienSx ,

    Thank you for your solutions, which work very well.
    Indeed, Table.Split and List.Split are very useful here.
    I slightly prefer the code below from AlienSx Thank you for your solutions, which work very well.
    Indeed, Table.Split and List.Split are very useful here.
    I slightly prefer the code below from AlienSix, even though the other codes are also very good.
    Thank you.
    Have a nice day.
    Best regards.:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    result = Table.FromList(
        List.Split(Table.ToList(Source, (x) => Text.AfterDelimiter(x{0}, " ")), 2),
        (x) => x, 
        {"Supplier", "Name"}
    )
in
    result


    Thank you.
    Have a nice day.
    Best regards.