PowerQuery Create Connection / Re-Use Query

[GERALDRAHMAN]

Hi All

I am new to Power Query. I would like to make use of It's automation functionalities. I have a PDF File that I need to convert to Excel format regularly. The formatting of the PDF is consistent/exactly the same, but the data varies. I have to Questions. Firstly, when I import the PDF File into PQuery, it "Opens" various queries/ or tables, although each table has different data, the "APPLIED STEPS" are the same for each Table, so if I carry out certain steps in Table 1, how can I apply the same steps to Table 2, without having to do exactly the same steps in Table 2 ? Secondly, Having done "all this" How can I save the "Template/Query/Connection, so that I merely need to import the File the next day/week and PQuery "remembers" the format, or applies all the steps to my current PDF file (whic has exactly same layout as initial PDF File on which I already performed Data Transformation etc....Thanks In advance.

 

[Worf]

Hi

• My Excel version does not support PDF import, so I did it with Power BI.
• You can add a new query by right clicking any column in Power Query and choosing “add as new query”.
• The advanced editor holds the applied steps, written in M code, corresponding to that query, as shown below.
• After creating a query, you can replace its code with another one, from another query. Copy and paste the steps from the desired query and manually adjust the file path and table identification.
• The saved steps will be applied to whatever file and table you plug into the code, as long as the format is compatible.

let
    Source = Pdf.Tables(File.Contents("C:\Users\Rxxxxx\Desktop\DAX basics in Power BI Desktop - Power BI _ Microsoft Docs.pdf"), [Implementation="1.3"]),
    Page1 = Source{[Id="Page005"]}[Data], // table Id
    #"Changed Type" = Table.TransformColumnTypes(Page1,{{"Column1", type text}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",6),
    Splitcol = Table.SplitColumn(#"Removed Bottom Rows", "Column1", Splitter.SplitTextByRepeatedLengths(33), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(Splitcol,{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}})
in
    #"Changed Type1"

 

[jdellasala]

After you select the PDF in the Navigator window, what button are you clicking? In 365 the buttons are Load with a dropdown, Transform Data, and Cancel.
Please add the version of Excel you're using and the OS in your Profile so we know which version you're working with.
In answer to your question about re-use of queries, I have a PDF that changes daily, but it's created by printing a web page, so I just overwrite the file whenever I want an updated report (all the queries are set to refresh on File Open). I know that doesn't answer the question directly, but it may resolve the issue.

As for re-using a Query:

1. Open the Source Workbook with the Query you want to copy, and open the Queries & Connections pane.
2. Open the New Workbook.
3. Go back to the Source Workbook and edit the Query.
4. In the Queries pane on the left in the Power Query Editor (click the < on the left side under the Ribbon). Right Click on the query you want to copy and select Copy.
5. Close the Editor and go to the New File and select Data -> Get Data -> Launch Power Query Editor.
6. Right Click in the Queries pane and select Paste.

Note that any Queries the selected Query needs will be brought in as well, so if there's more than one Query, you'll want to select the "last" one. You can view your Query's dependencies in the Power Query Editor in the View -> Query Dependencies window. This also works in Power BI.

That's the quick and dirty way, but if this is something you're going to do on a very regular basis, in the Source Workbook in the Excel Queries & Connections pane, Right Click on the Query you'll be re-using and select Export Connection File. You'll be presented with a File Save dialog box, and if the name of the Query is MyData, it will want to save the files as Query - MyData.odc in the My Data Sources folder which should be under the system's default Documents folder (which will be under OneDrive if you use it).
Now, in the new file go to Data -> Existing Connections, and you'll see Query - MyData listed. BTW, when you save the file, don't bother to try to save it with a name that doesn't start with "Query - ", it will still be listed as Query - MyData. As with the copy above, any queries the query depends on will also be brought in.
Unfortunately this does NOT work with Power BI, at least as far as my MS Store version with no server connection goes!

Finally, advice regarding Power Query and PDFs. I have found the one printed (from Chrome using Chrome's Print to PDF) to be inconsistent, and had to tweak it for more than a week to get it to work reliably. There are plenty of tricks to get the exact table you want and to deal with inconsistent headers, and plenty of help here.