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 transfor...
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

     

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.