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

[Scott]

I have the following:

Table 1
Serial Numbers	Invoice
123,124,125	333
126	444
127	555
128,129	666

and 

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

and I want:

Table 3
Serial Number	CusID	Invoice
123	ben	333
124	suzy	333
125	craig	333
126	allison	444
127	cindy	555
128	arun	666
129	amy	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? 

[GilbertQ · Accepted solution]

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.

[Scott]

That's fantastic. Thank you so much.