Cannot Convert Value of Type Table to Type Text (1 Viewer)

[tmyers]

I am getting this expression error in the query editor when connecting to a PDF with a table I am trying to link to.

let
    Source = Pdf.Tables(File.Contents("C:\Users\tmyers\Desktop\Commodity Price Files\Copper.pdf"), [Implementation="1.3"]),
    SplitEachColumns = List.Transform(
        List.Combine(
            Table.ToColumns(Source)
        ), each Text.Split(_, "#(lf)")
    ),
    TableFromColumns = Table.FromColumns(SplitEachColumns, Table.ColumnNames(Source))
in
    TableFromColumns

The reason I am doing this is the table in question, when it is brought in to the Excel sheet has all the data crammed into 2 cells on a row and I need the data to be separated.

Column1 should be:
10-10-10
8-8-8
6-6-8
6-6-6
4-4-6
4-4-4
2-2-4
2-2-2
Column2 should be separated by the $ and matched to the rows listed above in the same order.

Edit:
Here is the table in the PDF:

 

[The_Doc_Man]

You problem is easy for the 2nd column (just split along dollar-signs) but a real bear for the first one. If you had SOME kind of separator, that would be a piece of cake. But if there is nothing (not even a null) between the first and subsequent number groups, you are going to have a big problem. You would have to rely on some format rules, but the odds are against that rule being only the number of digits following the dashes. You can have a number with 2 digits (your 10-10-10 case) so when you get to the 8-8-8 followed by 6-6-6 case, their point of contact will be "-86-" which would defeat simple rules. This could probably done via a brute-force technique that uses "lookahead parsing" I suppose, but it would be far better if you could somehow force an extra character between the entries from the source level.

Are there any structural rules regarding the entries in the 1st column? Because that would be the ONLY way to assure a good split with what you showed us.

 

[Isaac]

I can't tell what program that is, apparently some com level api, but is there any chance that this

pdf")

should be

pdf"

It just seems early to close the parenthesis, given the fact that another apparent input parameter comes next?

 

[tmyers]

I can't tell what program that is, apparently some com level api, but is there any chance that this

pdf")

should be

pdf"

It just seems early to close the parenthesis, given the fact that another apparent input parameter comes next?

I would assume it is correct as it is system generated (I didnt type that part). As for program, it is M in the advance editor in Power Query which I am currently using in Excel. On a side note, I was kind of surprised the code selector in post didnt list M as an option.

Doc, I may end up just doing the split on the second column and manually match the part numbers to their cost rather that trying to do it all in Power Query.

 

[Isaac]

Ahh. Ok, it was just a shot in the dark.

 

[Isaac]

in that case, it seems like it is your calling code - and the context of that code - that would be the problem. Can you post that?

You posted the vendor-provided stuff, which probably is not changeable as far as what datatypes, object types it 'Returns' - but it's how you are calling and using it that seems like it would generate that error

 

[tmyers]

What you see is what I have. That is the code from the start of connecting to the source file and the attempt to split the columns apart. It worked once upon a time, which I forgot to mention so that is on me but I have no clue why it started throwing this error when it didn't prior. It isn't the end of the world as I can manually do the part numbers and split the pricing.

 

[Isaac]

What you see is what I have. That is the code from the start of connecting to the source file and the attempt to split the columns apart. It worked once upon a time, which I forgot to mention so that is on me but I have no clue why it started throwing this error when it didn't prior. It isn't the end of the world as I can manually do the part numbers and split the pricing.

I see. So the code is what it is, (what you've posted), and you get a runtime error when connecting to it via some Querying type of mechanism.
Interesting.

Is there a chance that the internal guts of the PDF is not what it is supposed to be?

I'm not a PDF expert, but I have definitely learned that what appears to the eye to be something in a PDF is definitely not necessarily what it is.

Adobe has even increased the intelligence of the Select tool beyond what may actually be the case in the guts of the thing you are selecting.
(in other words, you may be able to Select something that appears to be a table in a PDF, but actually isn't a strict table).

You could have an original PDF copy, for instance, that's perfect - but once people do something like Printing it to PDF again (via any number of opensource Print to PDF mechanisms), it may lose its guts.

 

[tmyers]

That is entirely possible as we get issued this file every few days as pricing changes (hence my automation of this).

 

[Isaac]

Hope you get it figured out.
Curious which api or product is this? an Adobe DLL that is exposed to vba ?

 

[tmyers]

Hope you get it figured out.
Curious which api or product is this? an Adobe DLL that is exposed to vba ?

That I couldn't answer. I just go to the Data tab and choose Get Data > From File > From PDF which then opens a window to select either specific tables or entire pages, at least on the files I am using. Once a selection is made, you can load it directly to the workbook without any fuss or go to Transform which then opens up Power Query. I then, as of late, go to Advance Editor and look at the M code that was generated for the connection then will either use the editor to do what I need to try to write out the M code to do what I need. I am not very versed in M at all so if it isn't something simple, I have to go asking or rely on the editor to do it for me.

I got into Power Query when I started delving into Power BI as you are in that a lot at first when building reports.

 

[Isaac]

Ahhh...I totally thought you were using some Adobe API or DLL or something in vba.
So the Let code is just straight from something that a PowerQuery advanced query editor generated while you used the editor?

 

[tmyers]

Ahhh...I totally thought you were using some Adobe API or DLL or something in vba.
So the Let code is just straight from something that a PowerQuery advanced query editor generated while you used the editor?

Correct. Power Query, no matter what application you launch it from (Excel, or any of the Power apps) all use M. It, along side Python has been the main language I have been trying to learn with some T SQL sprinkled in here and there. Have also been dabbling in DAX with Power BI.

 

[Isaac]

I may speak for more than myself when I say, I assumed on the OP that was VBA.

Thanks for clarification. Maybe I will give it a shot and see if I have any insights. for now thanks for your patience in bearing with my questions.

 

[Isaac]

Do you have a copy of that PDF you could share?

 

[tmyers]

Unfortunately I cannot. I probably shouldn't have shared the snip that I did

 

[Isaac]

when I go to New Source in power query editor, I don't get PDF as an option. nor in other sources

 

[tmyers]

when I go to New Source in power query editor, I don't get PDF as an option. nor in other sources
View attachment 108186

Under that and from file should be PDF. You skipped this step and went straight to Power Query which I dont think I have ever done.

 

[Isaac]

Ahh, ok. Well, I did just try that, and under File, I still don't have PDF, which brings me back to if you have a special add-in or API or something (?)

When I use ?Application.version in the vba immediate window, this is what I get:

16.0

 

[tmyers]

Interesting. I do have the Acrobat plugin, so maybe that is why that is an option for me. I have always had that plugin so would have never noticed if connecting to a PDF wasn't a "stock" feature of Excel.