Hello,
I recorded a macro which selects each pdf filepath in column A, transforms the data in powerquery, and then imports the table in a new tab. The macro worked well on the file with which I recorded the Macro. However, each PDF has a different number of columns I need to adjust as part of the powerquery transformation, and I was wondering how I can account for this in my code. Here is the snippet that is tripping the script up:
The error I get is "[Expression.Error] The name 'Source' wasnt recognized. Make sure it's spelled correctly". I suspect this is due to the fact that the VBA calls out specific column names/numbers (column 1, column 2, etc.) which reflects the original file, but other PDFs may not have those same columns. The one consistent rule I need the code to follow is that it should delete all columns not named "Id" or "Data", without the need to reference other columns by name.
Thanks!
I recorded a macro which selects each pdf filepath in column A, transforms the data in powerquery, and then imports the table in a new tab. The macro worked well on the file with which I recorded the Macro. However, each PDF has a different number of columns I need to adjust as part of the powerquery transformation, and I was wondering how I can account for this in my code. Here is the snippet that is tripping the script up:
VBA Code:
ActiveWorkbook.Queries.Add Name:="Table001 (Page 1)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""""" & filepath & """), [Implementation=""1.2""])," & Chr(13) & "" & Chr(10) & " #""Removed Other Columns"" = Table.SelectColumns(Source,{""Id"", ""Data""})," & Chr(13) & "" & Chr(10) & " #""Expanded Data"" = Table.ExpandTableColumn(#""Removed Other Columns"", ""Data"", {""Column1"", """ & _
"Column2"", ""Column3"", ""Column4""}, {""Column1"", ""Column2"", ""Column3"", ""Column4""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Expanded Data"""The error I get is "[Expression.Error] The name 'Source' wasnt recognized. Make sure it's spelled correctly". I suspect this is due to the fact that the VBA calls out specific column names/numbers (column 1, column 2, etc.) which reflects the original file, but other PDFs may not have those same columns. The one consistent rule I need the code to follow is that it should delete all columns not named "Id" or "Data", without the need to reference other columns by name.
Thanks!
