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

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