Split a column into two separate columns

[Mederic]

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

[AlienSx]

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]

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.