PowerBI API Integration

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.😊