June 17, 2020
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:
FY21 May-June forecast France.xlsx
FY21 May-June forecast Germany.xlsx
and so on
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?
July 16, 2010
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.
May 27, 2020
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.