Forum Discussion

KrisSok's avatar
KrisSok
New Member
2 years ago
Solved

Splitting multiple columns by delimiter into multiple matched rows

Hi   I have a large spreadsheet with 5 columns which can have multiple values (up to 200) separated by commas: Status Platform Exit Date INSIDE 25 PSERIES 19/04/2024 NO TREATMENT,NO T...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    In the NewTbl row, you can specify the column names. I have specified only Status and Exit Date.

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    NewTbl = Table.SelectColumns(Source, {"Status", "Exit Date"}), 
    Custom1 = Table.FromColumns(List.Transform(Table.ToColumns(NewTbl), (x)=> List.Combine(List.Transform(x, (y)=>List.ReplaceValue(Text.Split(y, ","),"",null,Replacer.ReplaceValue)))), Table.ColumnNames(NewTbl))
in
    Custom1
KrisSok's avatar
KrisSok
New Member
2 years ago

Thanks - think I get what you're doing - how do I specify the columns I want split? (There are 5 of them in a table of 30 columns)

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
2 years ago

In the NewTbl row, you can specify the column names. I have specified only Status and Exit Date.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    NewTbl = Table.SelectColumns(Source, {"Status", "Exit Date"}), 
    Custom1 = Table.FromColumns(List.Transform(Table.ToColumns(NewTbl), (x)=> List.Combine(List.Transform(x, (y)=>List.ReplaceValue(Text.Split(y, ","),"",null,Replacer.ReplaceValue)))), Table.ColumnNames(NewTbl))
in
    Custom1