Hello,
I put the background below, but if you prefer there is the directory for the Query file import and the issue statement right after for lisibility!
I work in a marketing team of a big company. Every month, we ask the 8 countries of the EMEA region to tell us how many units they want to sell for the next two months over a couple of products. To do so, I sell them a blank template that they have to return me. These files are put in a folder and merged automatically using Query. I create connections only for source, products and countries, and load everything else in the data model. Then, I use power pivot to display the result using 1 to many connections. Here is the hierarchy:
main_folder/
Forecast_overview.xlsx
source_files/
FY21May-June/
FY21 May-June forecast France.xlsx
FY21 May-June forecast Germany.xlsx
and so on
FY21June-July/
same
FY21July-August/
same
You see that we forecast twice the same month each time. How would I set Power Qwery so it keeps only the most recent column that I add?
Thank you!
Hi Alexis,
Welcome to our forum! Why don't you just remove the folders from the Source Data folder that you don't want included in your report anymore i.e. the old data? You could create a separate folder for this old data so that it doesn't sit under the aSource Data folder.
Mynda
Hi Alexis
I had once a similar task. I did what Mynda suggested:
- one folder "source" for all the files to be imported into power query
- one folder "backup" for the files which should not or no longer be imported
On top I suggest to get rid of your subfolders (e.g. FY21May-June/) and store the files directly. If you import the name of the file into your model as well, you can use that name in the power pivot as variable (e.g. via slicer) to select the estimate version you want. And you could compare the actual version with prior versions.
Regards
Franz
