Creating new Table and append

Hi EHell,

Thank you for reaching out to the Microsoft fabric community forum. I understand you need a solution to store historical data and manage table size over time while adding new daily data. Here’s a step-by-step approach in Power Query to meet your requirements:

Create a New Table for Historical Data: Create a new query in Power Query to store the appended data. This new table will allow you to retain the previous day's data and append the latest daily data without overwriting anything.

Group and Summarize Data: Use the Group By operation to group the data by your selected columns (e.g., Item and Status). Aggregate the numeric column (e.g., Count of Records) using an appropriate function (such as Sum or Count).

Add Today's Date: You can add a column with today's date using the function DateTime.LocalNow(). This will ensure each entry is associated with the date when the refresh occurs. Append the Data: After processing the data, append the transformed data into your historical table. This will ensure that new data is continuously added to the table without overwriting previous data.

Limit Table Size (Optional): To prevent the table from growing too large, you can filter out older records (e.g., keep only the last 30 days of data). You can use Power Query’s filtering options based on the current date.

I recommend reviewing the following Microsoft documentation for more information:
How to GROUP BY or summarize rows - Power Query | Microsoft Learn
Add a custom column - Power Query | Microsoft Learn
Append queries - Power Query | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.