Continuously grow a data source in power BI with new data

[henrycqc]

Hi

I am trying to automate a data processing activity done in Excel within Power BI.

Outline of the Excel process I am trying to automate in power BI:

• Each month I download new data from a website.
• This data is cleaned and then appended onto the bottom off previous data within an Excel file.  The data within this file continues to grow each month.
• The Excel file is imported into Power BI to update a dashboard.

My attempt to automate the above process:

• Step 01- Table 01 static; I imported the latest excel file and turned off do not include in refresh.
• Step 02- Table 02- I created a new query to dynamically extract data from a web page using the URL. The URL has two parameters for year and month which I can change to extract different monthly publications.
• Step 03 – I append the new website data in table 02 into Table 01. The aim is so that data will continue to grow each month I extract new data from the website.

Issue: Step 03 is not working as expected.

I can’t get table 01 to keep hold of the previous data extract from the website as well as add  to it any new additional extracts from the website for a different time period.

I also tried this: https://www.youtube.com/watch?v=xuBfK0oJmw8

[AnkitKukreja]

Hi @henrycqc 

The only solution I can think of now is to use RPA in Power automate to do the data extraction and then you can connect to the file directly. These might come in handy.

https://mspowerautomate.com/how-to-scrape-data-from-web-pages-in-microsoft-power-automate-desktop/

https://learn.microsoft.com/en-us/power-automate/desktop-flows/automation-web