cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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. 

 

Capture.PNG

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:

 

Capture.PNG

 

Thank you very much in advance.

 

Àlex

1 ACCEPTED SOLUTION

Accepted Solutions
Community Champion
Community Champion

Re: 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".

Specializing in Power Query Formula Language (M)

View solution in original post

11 REPLIES 11
Highlighted
Super User III
Super User III

Re: Split column in multiple rows

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.

 

SplitColumns.png

 

Regards

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Community Champion
Community Champion

Re: Split column in multiple rows

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

 

Specializing in Power Query Formula Language (M)
Highlighted
Helper I
Helper I

Re: Split column in multiple rows

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

Highlighted
Helper I
Helper I

Re: Split column in multiple rows

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

Highlighted
Community Champion
Community Champion

Re: Split column in multiple rows

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

 

Specializing in Power Query Formula Language (M)
Highlighted
Helper I
Helper I

Re: Split column in multiple rows

Thanks again Marcel! 

 

Still it appears this error:

Capture.PNG

 

Do you have any idea why it happened?

 

Thanks again and sorry for the inconvenience!

Àlex

Community Champion
Community Champion

Re: 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".

Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Helper I
Helper I

Re: Split column in multiple rows

Thank you so so much!!

 

It worked perfectly! Really appreciate your help and time.

 

Best regards.

 

Àlex

Highlighted
Helper I
Helper I

Re: Split column in multiple rows

Excuse me Marcel,

 

I have a fast question about the line "ExpandedTables". The reason of the number 2 of the end of the line is the result of 6 (number of columns) - 4 (number of columns with multiple items)?

 

I thank you again for your huge help.

 

Regards.

 

Àlex

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors