Forum Discussion

Mederic's avatar
Mederic
Post Patron
5 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
    5 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
5 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.