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
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!
Solved! Go to 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
thanks for your help 🙂
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...
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
62 | |
18 | |
16 | |
13 |