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".
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
ChangedType
Thanks 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".
Thank you so so much!!
It worked perfectly! Really appreciate your help and time.
Best regards.
Àlex