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:

StatusPlatformExit Date
INSIDE 25PSERIES19/04/2024
NO TREATMENT,NO TREATMENTPSERIES,PSERIES,
INSIDE 25,NO TREATMENTDATAPOWER,ECOSYSTEM31/12/2024,
NO TREATMENT,NO TREATMENT,INSIDE 25PSERIES,ISERIES,WINDOWS,,30/11/2024

 

I would like to split these into separate rows:

StatusPlatformExit Date
INSIDE 25PSERIES19/04/2024
NO TREATMENTPSERIESnull
NO TREATMENTPSERIESnull
INSIDE 25DATAPOWER31/12/2024
NO TREATMENTECOSYSTEMnull
NO TREATMENTPSERIESnull
NO TREATMENTISERIESnull
INSIDE 25WINDOWS30/11/2024


Any help would be really appreciated as I can do one column but have no idea how to do multiple columns!

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

6 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    Use this code. Source line may need to be replaced with your Source line which is generated post import of your data.

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.FromColumns(List.Transform(Table.ToColumns(Source), (x)=> List.Combine(List.Transform(x, (y)=>List.ReplaceValue(Text.Split(y, ","),"",null,Replacer.ReplaceValue)))), Table.ColumnNames(Source))
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
        Icon for Most Valuable Professional rankMost 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
  • KrisSok's avatar
    KrisSok
    New Member
    2 years ago

    this is the query I need to fit into:

    let

    Source = SharePoint.Files("https://zzzzz", [ApiVersion = 15]),

    Filter = Table.SelectRows(Source, each [Name] = "zzzz.xlsx"), File = Filter{[Name="zzzz.xlsx",

    #"Folder Path"="https://zzzz"]}[Content],
    #"Imported Excel Workbook" = Excel.Workbook(File),
    Relationships_Sheet = #"Imported Excel Workbook"{[Item="Relationships",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Relationships_Sheet, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Application ID", "Name_1", "Status_2", "Category", "Dev Subcategory", "Environment", "Operating System", "OS Service Pack", "OS Version", "Nlyte Report Location", "Normalised OS Info", "CMO RELATED?", "CMO DIRECT?", "VMWARE related?", "CMO Status", "CMO RISK", "Exit Date"})
    in
    #"Removed Other Columns"

     

    it's the last 5 collumns I'm trying to split 

     

    TIA

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Icon for Most Valuable Professional rankMost Valuable Professional
      2 years ago

      Do you need all columns in final output? If yes, I would like to see data in one the other columns also. Basically, I want to verify whether data in other columns also has comma like these 5 columns or not...