Forum Discussion

pect's avatar
pect
Helper I
8 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 separated by commas. 

 

Instead, I need to split columns "task", "start date", "finish date" and "checkpoint" into as many rows as different data are in each cell. Note that each cell has the same number of items for each id (i.e. id A has 3 tasks, 3 start and finish dates, and  3 checkpoints whereas id B has 4 of each). So, the table I am trying to model is the following:

 

 

Thank you very much in advance.

 

Àlex

  • MarcelBeug's avatar
    MarcelBeug
    8 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".

11 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 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
      8 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

       

      • pect's avatar
        pect
        Helper I
        8 years ago

        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

    • pect's avatar
      pect
      Helper I
      8 years ago

      Thank you very much Felix.

       

      I already tried what you propose but if I am afraid this is not what I am looking for. In your example your resulting table has 16 rows but it should only have 8 rows. The way you say it does a cartesian product resulting to repeated values I wouldn't need.

       

      Àlex