Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Issue: We publish monthly reports in Microsoft Power BI, featuring historical data visualized through graphs and charts. Our current Power BI file includes data extracted from an Excel file up to September 2024. Going forward, we plan to pull monthly data directly from a web source into a new query, appending this to the original Excel-based data, which will serve as our primary database.
To facilitate this, we created M code in the Power Query Editor, enabling automated data extraction from the website based on two parameters: month and year. During initial testing, our M code successfully extracted data for October 2024 and appended it to the existing database. However, when attempting to extract data for November 2024, the code overwrote the October data with November’s instead of appending it.
We tried multiple approaches to resolve this. First, we modified the web query to check if the selected month and year matched October 2024; if not, it would buffer the existing data and add the new data. Unfortunately, this still resulted in overwriting. Next, we attempted to buffer the combined tables in the historical data query after appending, intending to save it as the updated historical dataset. This approach also failed, as the append step continued to overwrite prior data.
Solved! Go to Solution.
Hi @Anonymous
Power BI reads the data at refresh time and doesn't store its historical version. Even if you buffer it in a separate query and choose not to load it, that query will still be evaluated if referenced by other loaded queries. If you want to include October data and not be pulled from API in the next refresh, you must use an external storage (excel, dataflow, another database, etc) and then append that in the query editor.
Hi @Anonymous
Power BI reads the data at refresh time and doesn't store its historical version. Even if you buffer it in a separate query and choose not to load it, that query will still be evaluated if referenced by other loaded queries. If you want to include October data and not be pulled from API in the next refresh, you must use an external storage (excel, dataflow, another database, etc) and then append that in the query editor.
It sounds like you're running into an issue with the appending logic in Power Query, where new data is overwriting rather than appending to the existing dataset. Here are a few steps you might try to get this working as intended:
1. Store Historical Data Separately: Consider creating a separate query just for historical data (up to September 2024) and then referencing this query in the main data pull. This would keep the historical data intact, and you’d only be appending new monthly data from the web query to this stored historical query.
2. Use a Staging Table: After pulling data for the new month (e.g., October or November), load it into a staging table first. Then, use a separate query to append the staging table with the historical data table, ensuring they stay distinct until the final append step.
3. Add an Index or Unique ID: If possible, add an index or unique identifier to each month’s data before appending. This can help Power Query recognize each row as unique and may prevent overwriting.
4. Control the Append Process: Instead of using dynamic M code that overwrites the table, create a snapshot table that stores each month’s data as a new entry. You could then consolidate all snapshots into one table on refresh, ensuring each month's data remains separate and is only appended.
5. Check Load Settings: Make sure your historical data query and the new data pull are both set to “Load to report” but not to overwrite existing data.
Power Query can sometimes be tricky with appends, especially when refreshing data. Give these a try, and feel free to reach out if you continue to encounter issues.
Please mark this as solution if it helps you. Kudos Appreciated.😊
You can try below steps hope it helps.
Modify M Code for Appending:
Make sure your M code explicitly appends new data to the existing dataset. Here’s a basic example of how you might structure your M code to append data:
let
Source = Excel.Workbook(File.Contents("path_to_your_excel_file.xlsx"), null, true),
ExistingData = Source{[Name="Sheet1"]}[Data],
WebData = Web.Contents("your_web_source_url"),
NewData = Csv.Document(WebData, [Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
CombinedData = Table.Combine({ExistingData, NewData})
in
CombinedData
Buffering Data:
Buffering can help manage data in memory, but it’s crucial to ensure that the buffering step is correctly implemented. You might want to buffer the existing data before appending new data:
let
Source = Excel.Workbook(File.Contents("path_to_your_excel_file.xlsx"), null, true),
ExistingData = Table.Buffer(Source{[Name="Sheet1"]}[Data]),
WebData = Web.Contents("your_web_source_url"),
NewData = Csv.Document(WebData, [Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
CombinedData = Table.Combine({ExistingData, NewData})
in
CombinedData
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.