Forum Discussion
Update Existing Table with Power Query
Hello... After watching countless YouTube videos, I'm still not able to find a solution to my tedious data entry problem.
I'm trying to figure out a way to update an existing table in an Excel file with data from PDF reports using Power Query. (I'm not sure if I'm describing that correctly.) Basically, I have a daily sales file that needs to be updated every day with data from PDF reports automatically emailed to me. Only certain data on the PDF is needed to be entered into the Excel file. The Excel file has a breakdown of daily sales by: Gross Sales, Sales Tax, Customer Credit Card Payments, Customer Cash Payments, Total Discounts, etc. The daily report is automatically generated and emailed over in PDF. Not only do these PDFs contain information needed to update the daily sales file, but it also contains other irrelevant data that does not need to go into the Excel file. I'm trying to automate this process since there are 20+ branches whose sales need to be recorded daily.
Hopefully such a solution exists that would help save some serious time (and reduce human errors!)!! Thanks in advance!
Hi goodie2vivs ,
Presuming you have Office 365 or a recent version of Excel:
If your PDF always has the same name and is overwritten each day:
1a) Data tab > Get Data > From File > From PDF
2a) Select the PDF that you want
3a) Perform transformations as required in Power Query
If you put all your daily PDFs with different names into a folder:
1b) Data tab > Get Data > From File > From Folder
2b) Select your folder
3b) Filter file list in Power Query to identify the files you want to use (you can filter on most recently created, file type etc.), then perform transformations as required
4) Once transformations are done, go to 'Close & Load' > 'Close & Load to...' and select a suitable place in your workbook to apply this query to a table.
5) Use this new table as a lookup source within your daily sales file to grab the new data that you need to report.
Pete
artemus Your answer is, I believe, incorrect in the context of what OP is asking. As you have "Microsoft Employee" next to your name it means people will assume that yours is the final word on the subject, and why I felt the need to highlight this error.
3 Replies
- AlB
Community Champion
Hi goodie2vivs
Share some sample data we can work on. One of these PDFs plus a pbix with the PQ operations you are currently doing, plus the expected result.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
artemusMicrosoft Employee
The only intended way to update a dataset using power query is by using Power Bi with the PowerApps visual.
- BA_Pete
Super User
Hi goodie2vivs ,
Presuming you have Office 365 or a recent version of Excel:
If your PDF always has the same name and is overwritten each day:
1a) Data tab > Get Data > From File > From PDF
2a) Select the PDF that you want
3a) Perform transformations as required in Power Query
If you put all your daily PDFs with different names into a folder:
1b) Data tab > Get Data > From File > From Folder
2b) Select your folder
3b) Filter file list in Power Query to identify the files you want to use (you can filter on most recently created, file type etc.), then perform transformations as required
4) Once transformations are done, go to 'Close & Load' > 'Close & Load to...' and select a suitable place in your workbook to apply this query to a table.
5) Use this new table as a lookup source within your daily sales file to grab the new data that you need to report.
Pete
artemus Your answer is, I believe, incorrect in the context of what OP is asking. As you have "Microsoft Employee" next to your name it means people will assume that yours is the final word on the subject, and why I felt the need to highlight this error.
