Power Query to split data (1 Viewer)

[tmyers]

I have a problem where a table being imported from a file has all its data crammed into a single cell. I know Power Query has the means to break it out, but after Googling it for a bit I cant seem to figure it out.

Here is the snip of my tiny table:

As you can see, all my values are in a single cell and I need to break each entry out into its own row. Could someone provide assistance please?

 

[Minty]

Look at the Split Column function.
It takes

BT Mobile, EE, GiffGaff, iD Mobile,O2,Plusnet Mobile,Sky Mobile

And turns it into

 

[tmyers]

Minty, I was able to get closer to what I am after using Split Column using Delimiter Line Feed #(lf) and separated it into the rows. Problem is it is now repeating each value in column 1 for each value in column 2. Any idea what the next step would be?

 

[arnelgp]

it's transformation only so the orig data is intact, therefore you can Delete the other column?

 

[Minty]

Can you demonstrate your desired output based on the original input?
(if you have a sample rather than a picture that would be better)

 

[tmyers]

Attached is a example. Upper table is what the query outputs without any transforming being done and the lower table is what I am trying to transform it to.

Edit:
I just noticed I missed a row in the desired table which is why the size and price dont line up. Can correct if it is needed.

 

[CJ_London]

Excel doesn't always get it right - what is the original source? a text file? xml?

 

[tmyers]

A PDF unfortunately. If it were any other file, I would edit the source but being that it is a PDF it has been a royal pain.

Edit:
The PDF is originally from an Excel file that has been converted to PDF (I dont have access to the original) so it actually plays pretty nice as a data source except for this one particular table. Everything else can be queried with only minor tweaks within Power Query.

 

[CJ_London]

Not my area of expertise but there appear to be a number of vba solutions if you have adodb reader

access vba read a pdf table at DuckDuckGo

DuckDuckGo. Privacy, Simplified.

duckduckgo.com

and there are a number of adobe libraries available

 

[Minty]

I just noticed I missed a row in the desired table which is why the size and price dont line up. Can correct if it is needed.

Please correct it it makes no sense as it stands?
I can't see any corrrelation or real difference between the starting data (That's what we really want, not the existing query) and the output?

This is the current output of the query
SEU	(Do not cut)
10-10-10 8-8-8 6-6-8 6-6-6 4-4-6 4-4-4 3-3-5 3-3-3 2-2-4 2-2-2 1-1-1 1/0-1/0-1/0 2/0-2/0-2/0 3/0-3/0-3/0 4/0-4/0-4/0	$1,128.29 $1,327.31 $1,765.84 $2,007.43 $2,791.64 $3,148.51 $3,672.23 $3,929.33 $4,255.35 $4,901.11 $6,471.35 Call $10,030.72 $12,698.63 Call
This is the desired output
SEU	(Do not cut)
10-10-10	$1,128.29
8-8-8	$1,327.31
6-6-8	$1,765.84
4-4-6	$2,007.43
4-4-4	$2,791.64
3-3-5	$3,148.51
3-3-3	$3,672.23
2-2-4	$3,929.33
2-2-2	$4,255.35
1-1-1	$4,901.11
1/0-1/0-1/0	$6,471.35
2/0-2/0-2/0	Call
3/0-3/0-3/0	$10,030.72
4/0-4/0-4/0	$12,698.63
	Call

 

[tmyers]

Here is the corrected output:

SEU	(Do not cut)
10-10-10	$1,128.29​
8-8-8	$1,327.31​
6-6-8	$1,765.84​
6-6-6 * this is the entry that was missing	$2,007.43​
4-4-6	$2,791.64​
4-4-4	$3,148.51​
3-3-5	$3,672.23​
3-3-3	$3,929.33​
2-2-4	$4,255.35​
2-2-2	$4,901.11​
1-1-1	$6,471.35​
1/0-1/0-1/0	Call
2/0-2/0-2/0	$10,030.72​
3/0-3/0-3/0	$12,698.63​
4/0-4/0-4/0	Call

If you notice on the first table all the data for each column is all within a single cell. I need it to be broken out in rows like the example above so I can run other formulas and such against it elsewhere in the sheet whereas I cannot do that if all the data is in a single cell.

If push does come to shove, I can skip this particular table as it is one of the smallest I am importing and I can just have it be manually entered.

 

[arnelgp]

using VBA.