Power BI pick up the latest files in the folder

[ZozoeyWang]

I am building a data model to present a report have rolling 12 months data. This requires power query always pick up the latest 12 files in the folder. I have used this function before however I am not able to replicate this any more. From memory, you can put the actual number of files in the folder you want power query to load every time you refresh the model, for example, if you want the latest 12 months, you put 12 , if you want latest 24 months, you put 24. Of course, the files would have common name except the date components.

I hope I have made myself clear what I am after. I am not able to find any relevant blogs or videos talk about this. Please can some one give me some directions. Many thanks.

[v-lili6-msft]

hi, @ZozoeyWang

     You may try to use this way:

Step1:

In Edit Queries, Sort by date in descending order

Step2:

add an index column

Step3:

filter the index column like this

Best Regards,

Lin

[ZozoeyWang]

Hi Lin,

Thanks for your input. 

I've tried both adding index in combine files stage and edit stage. However it didnt work.

Thanks anyway.

[CheenuSing]

Hi @ZozoeyWang

Can you please share some details about the file names available in the folder.  If you can post some last 6 months files and share it here to work out a solution.

Cheers

CheenuSing

[Ashish_Mathur]

Hi,

Ideally the folder should be having all historical files.  Via DAX formulas, you should be able to compute metrics for rolling 12 months.

[ZozoeyWang]

Hi,

Good to see DAX can potentially resolve this, but if I am not mistaken there is a dialogue box for you to overwrite how many files in the file you would like power bi to process/load. And yes the folder would have all the historical files, but I only need the top 12 for example.

Thanks

[Ashish_Mathur]

Hi,

May be there is a way.  Someone else will help you.