Forum Discussion
Split column in multiple rows
Yes, I overlooked one required adjustment: there is still a "Source" in that line of code, that should be adjusted to #"Promoted Headers".
Once you splitted the texts, you can combine the nested lists into tables and have these expanded, to prevent arriving at the cartesian product of all combinations.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SplittedTexts = Table.TransformColumns(Source,{{"task", each Text.Split(_,",")}, {"start date", each Text.Split(_,",")}, {"finish date", each Text.Split(_,",")}, {"checkpoint", each Text.Split(_,",")}}),
AddedTables = Table.AddColumn(SplittedTexts, "Tables", each Table.FromRows(List.Zip({[task],[start date],[finish date],[checkpoint]}))),
RemovedColumns = Table.RemoveColumns(AddedTables,{"task", "start date", "finish date", "checkpoint"}),
ExpandedTables = Table.ExpandTableColumn(RemovedColumns, "Tables", {"Column1", "Column2", "Column3", "Column4"}, List.Skip(Table.ColumnNames(Source),2)),
ChangedType = Table.TransformColumnTypes(ExpandedTables,{{"id", type text}, {"project", type text}, {"task", Int64.Type}, {"start date", type date}, {"finish date", type date}, {"checkpoint", type text}}, "en-GB")
in
ChangedType
Thank you very much Marcel.
I am trying to apply the script you send me but I am afraid I am not properly introducing it. I explain the steps I followed:
- First of all, I loaded the table from Excel and entered in the Power Query.
- Then, I promoted headers as by default the headers are "ColumnX".
- Afterwards, I went to the View>Advanced Editor and saw this code:
let
Source = Excel.Workbook(File.Contents("C:\Users\aoctavio\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true])
in
#"Promoted Headers"
- There is where I am having trouble introducing the script you kindly sent me.
Could you please help me with this doubt? I'd really appreciate it.
Thanks again.
Àlex
Steps to take:
- Copy my code from step "SplittedText" downwards,
- paste that before your "ïn",
- add a comma at the end of your step #"Promoted Headers",
- adjust Source to #"Promoted Headers" in step "SplittedText"
- delete your last 2 lines.
let Source = Excel.Workbook(File.Contents("C:\Users\aoctavio\Desktop\Book1.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), SplittedTexts = Table.TransformColumns(#"Promoted Headers",{{"task", each Text.Split(_,",")}, {"start date", each Text.Split(_,",")}, {"finish date", each Text.Split(_,",")}, {"checkpoint", each Text.Split(_,",")}}), AddedTables = Table.AddColumn(SplittedTexts, "Tables", each Table.FromRows(List.Zip({[task],[start date],[finish date],[checkpoint]}))), RemovedColumns = Table.RemoveColumns(AddedTables,{"task", "start date", "finish date", "checkpoint"}), ExpandedTables = Table.ExpandTableColumn(RemovedColumns, "Tables", {"Column1", "Column2", "Column3", "Column4"}, List.Skip(Table.ColumnNames(Source),2)), ChangedType = Table.TransformColumnTypes(ExpandedTables,{{"id", type text}, {"project", type text}, {"task", Int64.Type}, {"start date", type date}, {"finish date", type date}, {"checkpoint", type text}}, "en-GB") in ChangedTypeThanks again Marcel!
Still it appears this error:
Do you have any idea why it happened?
Thanks again and sorry for the inconvenience!
Àlex
Yes, I overlooked one required adjustment: there is still a "Source" in that line of code, that should be adjusted to #"Promoted Headers".