Splitting multiple columns by delimiter into multiple matched rows

[KrisSok]

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 TREATMENT	PSERIES,PSERIES	,
INSIDE 25,NO TREATMENT	DATAPOWER,ECOSYSTEM	31/12/2024,
NO TREATMENT,NO TREATMENT,INSIDE 25	PSERIES,ISERIES,WINDOWS	,,30/11/2024

I would like to split these into separate rows:

Status	Platform	Exit Date
INSIDE 25	PSERIES	19/04/2024
NO TREATMENT	PSERIES	null
NO TREATMENT	PSERIES	null
INSIDE 25	DATAPOWER	31/12/2024
NO TREATMENT	ECOSYSTEM	null
NO TREATMENT	PSERIES	null
NO TREATMENT	ISERIES	null
INSIDE 25	WINDOWS	30/11/2024

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

[KrisSok]

thanks for your help 🙂

[KrisSok]

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]

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

[Vijay_A_Verma]

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]

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 · Accepted solution]

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