Adding new data to a sheet without refreshing an already loaded data

[ItzJoshy]

Hi everyone

Im not sure if I have the right subject but I hope you can help me with this 

I have 2 source files. 1 is extracted and updated daily (NPS Raw) the other (Staff file) is updated as needed

The problem lies with the staff file. I have a consolidated sheet that takes the data from 2 folders. The NPS folder where all the raw data for NPS is extracted and copied into. Thr other folder is where the staff file is and it is merged to the NPS query so that I get the respective staff details for the NPS data. I need my data to show who the manager of the staff is during the time the NPS was received however, if I load a new staff file and refresh the consolidated workbook, it will change all the existing staff file

Ex: from Jan 1-15 agent A's manager is Manager A. However agent A changed managers from Jan 16-30 to Manager B. When I change the data on the source path, it will change the Agent A's manager to Manager B from Jan 1 - 30 which affects the overall peformance of Manager A and B 

The alternative I can think of is to create a new folder for both files whenever there is a change in the staff file. I hope there is a better way to address this.

[BA_Pete]

Hi @ItzJoshy ,

There better ways to address this than creating new folders:

1) A data warehouse with an SCD table build in the ETL.

2) Possibly Incremental Refresh, but you'd need to be very careful about the IR configuration and your staff file update frequency to ensure no gaps, as well as a number of other caveats:

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

3) Use a folder connector and add new snapshot staff files to the folder at regular intervals, but never delete/replace any. You could then build your own SCD table from this.

Pete