cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Scott Frequent Visitor
Frequent Visitor

How to split a variable number of items in a cell into rows, and copy the contextual column data

I have the following:

 

Table 1
Serial NumbersInvoice
123,124,125333
126444
127555
128,129666

 

and 

 

 

Table 2
Serial NumberCusID
123ben
124suzy
125craig
126allison
127cindy
128arun
129amy

 

and I want:

 

Table 3
Serial NumberCusIDInvoice
123ben333
124suzy333
125craig333
126allison444
127cindy555
128arun666
129amy

666

 

The trick is that the number of entries in "Serial Numbers" is variable, and the data comes from a Web Query, so a 1-time conversion in Power BI Desktop won't work on an ongoing basis. 

 

Help? 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to split a variable number of items in a cell into rows, and copy the contextual column data

Hi @Scott

 

What you can do is if you have both your tables in the Query Editor, you can split Table1 by Delimeter (which will be the comma) and if you click on the Advanced Options you can then select to "Split Into - Rows"

 

 

Then once that is done you can then Merge Table1 with Table2 joining on the Serial Numbers. Which should then give you the final output for Table3.


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog

View solution in original post

2 REPLIES 2
Super User
Super User

Re: How to split a variable number of items in a cell into rows, and copy the contextual column data

Hi @Scott

 

What you can do is if you have both your tables in the Query Editor, you can split Table1 by Delimeter (which will be the comma) and if you click on the Advanced Options you can then select to "Split Into - Rows"

 

 

Then once that is done you can then Merge Table1 with Table2 joining on the Serial Numbers. Which should then give you the final output for Table3.


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog

View solution in original post

Scott Frequent Visitor
Frequent Visitor

Re: How to split a variable number of items in a cell into rows, and copy the contextual column data

That's fantastic. Thank you so much.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,119)