Power Query - Historical Data

[RayMundo00]

Hi everyone

I hope anyone can help me with my problem. Here is the thing:

I daily receive a report with the materials that are pending to send to the next node in the Supply Chain (from Warehouse to the Distribution Center). Once I receive the repot, I use Power Query to apply some steps and Join the data with other table to add additional information. The problem is that when a material is no longer pending, the material is removed from the report I receive daily, so the next day I have fewer lines than the previous day and I would like to keep those lines from the previous report for the tracking of that material.

Could someone help me with a strategy to keep the lines that are being deleted, since I still need to monitor other activities that come from the information added through Joins from other tables?

This is a image describing the problem and the desire outcome.

[v-yueyunzh-msft · Accepted solution]

Hi , @RayMundo00 

According to your descripiton, Do you want to get historical data refreshed every day?

First of all, Power Query is a management tool for data connectors. When the data of our data source changes, it cannot save the previous data and only reads the data that exists in the current data source.

If you want to keep the historical data for each refresh, then create a new table to save the daily refresh data. The first way is to obtain the data refreshed every day through excel export.

Export data from a Power BI visualization - Power BI | Microsoft Learn

Another way is to create a dataflow Gen2(This is the new feature in Fabirc), then set the destination of the saved data to the place you want to store, and then select the "Apeend" save mode; in this way, the data you refresh every day will be stored in your Apeend in the destination. You can connect the destination's data again, and then distinct data to achieve your needs.

For more information, you can refer to :
mslearn-fabric (microsoftlearning.github.io)

Differences between Dataflows Gen1 and Gen2 - Microsoft Fabric | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly