Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
KrisSok
New Member

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!

1 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

View solution in original post

6 REPLIES 6
KrisSok
New Member

thanks for your help 🙂

KrisSok
New Member

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

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
Super User
Super User

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

 

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)

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors