Retain Data from Datasource that updates

[mundy727]

Hi,

Im working with a dataset that updates daily using a web data source. In this dataset, there would be specific data that is there for one day, then that data would be overwriten from the source the following day and new data would populate. Using power query in excel, is there any way to retain the data to have a list continue to accumulate of the data each day?

For example below:

Data day 1

#	Name
1	John
2	Jane

Data day 2

#	Name
3	Bill
4	Bob

Data Cumulative

#	Name
1	John
2	Jane
3	Bill
4	Bob

[ppm1]

You could do this with incremental refresh but if you ever refresh the whole dataset, you'll lose everything. A better approach would be to create a Power Automate flow that queries your dataset daily and writes the data out to a csv on SharePoint (or wherever). You can then build your Power BI report from the csv files.

Pat

[mundy727]

Thanks Pete! I looked into the Incremental Refresh. If i were to pull in the data as mentioned, am i setting this up right as shown attached? Range start date paramater is set to the oldest date in time captured in the data set and Range end date is current date.

[BA_Pete]

Hi @mundy727 ,

Power Query is a mashup/transformation tool, not a data warehouse.

The Power Query refresh process goes like this:

Wipe all data > Import data from source > Perform transformations > Push output to required destination

So, no, there isn't a native way to retain data.

Within Power BI, the closest you would get would be Incremental Refresh:

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

Within Excel, you would likely either need to set up a proper data warehouse and appropriate ETL processes, or have your data saved into a network folder or SharePoint folder each day, then use the Power Query Folder/SharePoint Folder connector to bring in all files and aggregate/append into a single large record:

https://learn.microsoft.com/en-us/power-query/connectors/sharepoint-folder 

There'll be someone along in a minute that will reply with a hack of how you can dump data into a worksheet, then loop it back into Power Query, then dump it into a worksheet etc. etc. but these methods are just that: hacks. They are in no way suitable for any type of production or stable environment.

Pete