Forum Discussion
Power ParticipantRefresh Excel files when new file is placed in folder every Monday
Community Championashishrj Could you please clarify the below
1. Is every week the file will be replaced i.e. only one file with only cummulative hours data (in one of the field) will be available.
2. Will there will be previous any weeks hours data in the weekly file
If your answers to above are 1.Yes 2.No then I'm afraid that you can't really achieve that. Because, as soon as you refresh it will replicates the data available on the file and it will not store the previous data. You might need to have proper ETL mechanism to store the previous weeks data and stamp the loaddate or weekendingdate to it... Keep appending every weeks file to the same table and import that table into PowerBI.
Power ParticipantPattemManohar Thanks for your prompt response.
1. File can be replaced but if required I can keep it as a seperate file altogether.
2. No. There won't be any previous week hours in the new file. We need to calculate current week value from the previous week stored value
i.e. current week value = latest value from file - previous week value (which is stored or recorded in some table or column)
So, the challenge is:
1. Storing previous week data
2. Calulating current week data as there needs to be a relation defined between the previous week data stored and the current week data
3. Automate all of this process so as to minimize human intervention of manually calculating / renaming anything
- PattemManohar
ashishrj If you can have multiple files in the same folder without overriding the previous file, then you can follow the below approach.
1. Connect to the folder where all the source files are placed using "Folder" as source type under "Get Data"
2. You can see the below screenshot, then click on the "Combine Files" little icon on the "Content" column
3. Then the data from all the files are combined as below with having sourcename as well
4. As and when you got new file, just refresh the data and it will be in the same structure without any change required as below
Then you can calculate the difference hours as we always have historic weeks data as well.
- ashishrj
PattemManohar Thank again. I am afraid whether this will work in my project. Let me just check and let you know for any issues / challenges faced if any.