Rolling 13 weeks average to update and replace with new data

[Anonymous]

Hi all,

I currently get a rolling 13 weeks file with updated sales each week split by category, subcategory and store. How do I upload these files each week to ensure that the latest file's data are overwritten with the new sales data? 

i.e.: Week 5: Sales of Cat A Store 1 is $100

the next week I receive a file that sales Sales of Cat A Store 1 Week 5 is $150 

Thanks

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

Please have a try.
Name the two excels with the same format and put them in the same folder.
Steps in Power Query:

1. Get data in desktop: enter the folder path.
2. Filter the excel files you want based on the creation date or modification date.
3. Expand content to get data.
4. Get the latest data when refresh.

More details; Automatically update data according to the date in the file name 

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Thank you! I will give it a try tomorrow.

[v-rongtiep-msft]

Hi @Anonymous ,

Could you please tell me what is your data source? If it is a CSV file, I agree with @hmahesh94 's way. Please refer to it. I have also found a blog, please refer to it to see if it helps you.

Import all CSV files from a folder with their filenames in Power BI 

• It only worked on CSV files 
•  If your CSV files contained crucial meta data in the filename, you “lost” that data after clicking the combine binaries button.

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Here are the 2 files. I can easily convert them to csv files. However I am not sure how do I upload them in Power BI and ensure the rows do not get duplicated.31.01.22 ext 10.01.22 extract 

[hmahesh94]

Hi @Anonymous 

Assuming you are connected to a CSV file:

   You can replace the previous weeks file with the updated file at the same location provided you won't be needing data from that file. Also, please remember to rename the file and the structure of the file should not be changed. Then, a simple refresh should update the underlying data.

The other way to approach this is to change the source of your file each week.

You may also go for a better solution by using Power Automate to deal with these situations.

Please feel free to ask for clarification. 
Cheers!

[Anonymous]

Thank you for your reply. If I were to replace the previous weeks file, wouldn't the data be duplicated i.e. 2 rows of the same data?