Keeping snapshot of past data and appending new data to the same datasource

[akkitek]

Hey Everyone,
Hope you're well. We use a PSA software for our consulting and generate monthly reports within them. Once the report is run, we generate a CSV url from it and plug it into Power BI as a Web page for our additional BI needs. 
For example: We run a report for july and plug it into PBI. The following month when the same report is run for August, it overwrites the data for July. The web page URL does not change so the PBI scheduled refresh runs smoothly, helping us make our pbi reports dynamic. But it the new pbi report we only have data for august. Is there a way I can keep the old data in power bi and append the new data to the existing data table after the scheduled refresh? MSFT documentation says that as this is a web page, incremental refresh is not supported. Is there a workaround for this? Any help is appreciated! Thanks in advance!
@amitchandak @Greg_Deckler 

[v-angzheng-msft]

Hi, @akkitek 

If you find that viewing the native query is not available, you are using a step that cannot be converted to a SQL statement.

When the report refreshes with only August data, it means that the linked data source only has data for August. Try keeping the data for the previous month and appending the additional month to the data source, updating the URL link.
Alternatively, create another query each month to retrieve the data, and then merge the two queries together in the PowerQuery editor.

See links below for more information:

Power BI Native Query and Query Folding 

Power Query query folding 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[akkitek]

Hey @v-angzheng-msft ,
Thanks for your reply. I can keep the the old data as it is and add the new data with a updated URL and append the query each month. This will work but as we have multiple clients having the same request it wouldn't be sustainable for us to do that every month. I tried following this blog: https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...

and tried to replicate the M query like this:

And now I get a different error. 

I'm not sure if this is a sustainable method either. In an other BI tool we were using in the past, this was very simple and we would just select if we want the new data to replace the old data or append. Looking forward to hearing from you. Thanks in advance!
Best,
akkitek

[v-angzheng-msft]

Hi, @akkitek 

I don't know much about this method, this method seems to work, but the site can be accessed without authentication, which means that the data is public.

Best Regards,
Community Support Team _ Zeon Zheng

[sevenhills]

Direct pull:  Please check and see if this helps in your scenarios:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure 

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla 

Folder: Files pull

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#:~:text=With%20i....

[akkitek]

Hey @sevenhills,
Thanks for these links. I did a little bit of digging and tried the incremental refresh option out but it only works if the data source supports query folding. and "web" datasources do not support query folding so incremental refresh is not recommended. 

I set the parameters (range start & range end) and filtered the data based on the parameters but it doesn't let me set incremental refresh policies. I get this error inspite of setting the parameters.

Is there another way I could append the old data with the new refreshed data? Looking forward to hearing from you.
Best,
akkitek.

[sevenhills]

Sorry, I did those steps in the past and worked. 

Check these links if it helps

https://www.youtube.com/watch?v=RnrCKn5ykCc 

https://www.youtube.com/watch?v=QEFze-LdLqo

[akkitek]

Hey @sevenhills ,
Thank you for your reply. I watched the above videos. The first video he uses a SQL server database which supports query folding and thus supports incremental refresh.
The second video and the microsoft documentation both say that incremental refresh is only recommended if there is query folding. The second video also says, if you can see the option to "view native query" query folding is taking place. In my power query editor, that option is disabled bacause my data source is a web page.

[sevenhills]

Sorry I dont know.

The videos spoke how to check and for me these fixed in the past and hence shared.

[akkitek]

No worries! Thanks for your help!