Power BI - Automatically identify new folders to get data and combine them

A new year is coming and the path where we save our data is changing.

The problem is: the data from each asset is saved in folders with the current year as it's name, so data from 2022 is saved in a folder called 2022, and so on.

I want to make a query that will autamatically indentify what years (folders) we have data from and combine them.

The data is saved in this path:

C:\Users\Projects\3. Assets\Type A\Asset Name\Control\YEAR\Data\Dataset\excel.xlsx

This asset for instance has 3 years of data 2020, 2021 and 2022.

By next week we will already have a 2023 folder with new data, usually a manually add a table.combine to the query, but we have a large number of assets and it can be tricky.

Someone knows a efficient way to automatically identify all the folders named with a year and combine the excel data inside them ?

This is the way i usually do:

Table.Combine ({Sharepoint("...2020/Data/Dataset"),Sharepoint("...2021/Data/Dataset"),Sharepoint("...2022/Data/Dataset")})

Sharepoint is a function that returns folder content from sharepoint.

Best Regards

Solution

Can you pull all the directory names at a higher level

then filter them ?