Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 |
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
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.
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
Proud to be a Datanaut!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |