Update data on monthly basis from new pdf table

[Anonymous]

Hello all,

 I am fairly new to PowerBI and I am still exploring the possibilities of PowerQuery and loading data.

The data is loaded to power is contained in the pdf report. I choose the Folder connector and I choose which pdfs and which tables from these pdfs to be loaded to PowerQuery.The data is in a similar format in each PDF and the position of the table is also the same in each one of them. A new PDF is added each month. There are certain transformations I apply to the table. What  I would like to do is to automate the process of transforming this data for each new pdf table and also automatically append it to the initial transformed table when I click 'Refresh' on monthly basis. What would be the way to achieve that?

Thanks in advance for your responses.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Your logic is clear and correct. When you use Folder connector to combine files, you will select a file as sample file. You just need to perform transformations in "Transform Sample File" query, then all transformation steps in this query will be applied to all files that have been combined. The combined result is in "Other Queries" folder. 

Next time, you just need to add new PDFs into the same folder, then click "Refresh" button. Power BI will reconnect to this folder, extract all files to perform transformations and combine them. In a refresh, Power BI will remove old data and import all data again. Your old data and new data will be combined into a table in a new refresh. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[AlexisOlson]

Regarding automatically appending, when you hit 'Refresh' Power Query loads everything from scratch (it doesn't store what it had before and append to it).

More detail about this aspect:

https://community.powerbi.com/t5/Power-Query/Record-and-Store-Values-From-One-Table-to-Another/m-p/2...

[serpiva64]

Hi,

yes i think there is the way.

But i think that without some sample data it will be very difficult to answer your questio.