Need help appending new rows to existing rows when refreshing with a date component

[Anonymous]

I would like Power BI to perform a daily refresh of an Excel file. However, during the refresh process, I want it to avoid updating the existing values in the file and instead add new rows from the Excel file to the existing rows. Additionally, I would like the appended values to include the date on which they were added to Power BI. Is this achievable?

The Excel file in question serves as the outstanding purchase orders file and operates on a rolling basis. This means it contains orders that are yet to be fulfilled. When an order that was placed arrives, the corresponding entry is deleted. My goal is to use this file to track day-to-day changes in outstanding incoming orders.

[Anonymous]

Hi @Anonymous ,

Maybe the incremental refresh helps you. Please refer to

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

When performing an incremental refresh, the historical period is not refreshed, only the refresh period set in advance.

Best Regards,

Stephen Tao

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

[Anonymous]

Hi Stepher, 
I donn't think incremental refresh would work for my case because I do not have a date component or anything that I use to filter. For example, it would be like connecting to a folder that saves a copy of the dataset on a daily basis but with a single file. I want Power BI to take a snapshot of the data every day at one particular date and add the data to a master table with the date it was snapshotted. Lets say the data on Day 1 has 15 lines and data on Day 2 has 17 lines. The Master table would have the data from day 1 and day 2 with a total of 32 lines with a differentiator which would be the date of the snapshot. 

[rubayatyasmin]

here is another idea, though this could be a workaround. 

- Duplicate your base query.

- then in the duplicated query remove the steps that modify existing data or any operation that you want to avoid during the refresh. 

- Create a new query that loads data from your Excel file.

- Remove the rows with orders that have been fulfilled or any unwanted data.

- Add a custom column that contains the current date using the formula DateTime.Localnow().

- Then in the duplicated query append the newly created query. 

- then in power BI service schedule a daily refresh in the dataset

[Anonymous]

I am not sure if this would work because it would mean I would have to remove/add rows with completed/new orders every day. 

[rubayatyasmin]

Hi, @Anonymous 

You need to save your data in a database. Use ssms or any database at your disposal to achieve this goal. 

Did I help? If yes, hit 👍 and accept my solution as answer. 

[Anonymous]

Is there a way to do this without importing the data to an SQL server?