Forum Discussion

pect's avatar
pect
Helper I
9 years ago
Solved

Split column in multiple rows

Hello,   I'd be very grateful if you could help me with an issue I've been struggling all the day through.   I have data in below format (1st image) where some columns have multiple values separa...
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Yes, I overlooked one required adjustment: there is still a "Source" in that line of code, that should be adjusted to #"Promoted Headers".

MFelix's avatar
MFelix
Super User
9 years ago

Hi pect,

 

You need to use the split into columns mode but with the option split to rows you need to do this for every column below see the steps and final result.

 

 

Regards

MFelix

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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