Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
h0ttamale
Frequent Visitor

How do I duplicate rows based on cell contents (cell contains semi-colon seperated data)?

How do I duplicate rows based on the content of Column B. I'd like a separate row for each "Person" in the cell?

This is my starting table (I can't extract the data in any other way):

 

Image 2.png

This is my goal:

 

Image 3.png

 

I'm new to BI but familiar with databases. Does it make more sense to "text to columns" then do a join?

 

Please help and thank you!

1 ACCEPTED SOLUTION
tringuyenminh92
Memorable Member
Memorable Member

HI @h0ttamale,

 

This could be done by Query Editor (M Language) as below steps:

  • Click Edit Queries 
  • In Query Editor, Choose Add Column tab -> Choose Custom Column
  • Name for new column: NewContact with formula: Text.Split([Contact],";")
  • In the top right of NewContact column, choose Expand to new rows

Screenshot 2017-06-24 20.34.14.pngScreenshot 2017-06-24 20.32.51.pngScreenshot 2017-06-24 20.33.02.pngScreenshot 2017-06-24 20.33.40.png

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

View solution in original post

5 REPLIES 5
say241
New Member

Hello, I have the same problem with a dataset that has multiple countries(e.g US, UK, Mexico, Canada) in a cell that is supposed to have just one country(e.g US ) in the cell under 'Country' column. I followed the steps you gave above in power query editor for but I could not find the option 'expand to new rows'.

 

Kindly assist

tringuyenminh92
Memorable Member
Memorable Member

HI @h0ttamale,

 

This could be done by Query Editor (M Language) as below steps:

  • Click Edit Queries 
  • In Query Editor, Choose Add Column tab -> Choose Custom Column
  • Name for new column: NewContact with formula: Text.Split([Contact],";")
  • In the top right of NewContact column, choose Expand to new rows

Screenshot 2017-06-24 20.34.14.pngScreenshot 2017-06-24 20.32.51.pngScreenshot 2017-06-24 20.33.02.pngScreenshot 2017-06-24 20.33.40.png

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

what if your reference cell contains numeric value, and that numeric value shoud be the times it needs to be replicated? Lets say, my cell value for the row is 5, then I want to have 5 copies of that rows. Thanks!

 

Anonymous
Not applicable

then you should add a Custom Column with a formula:

List.Repeat({[your reference cell]},[your reference cell])
and then in the top right of your new Custom Column choose Expand to new rows

Awesome! Thanks! Works perfectly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.