Power query logic to pick the current year folder

[Anonymous]

Hi Power BI aficionados

So we have a bunch of Power BI reports whose sources are yearly folders maintained on our corporate network. At the end every year, we need to repoint the folder to the present year in order for the report to render current year information. For example,  I've had to manually change the source folder from 2019 to 2020 once we transitioned into 2020. Is there a way we can automate this? In other words, is it possible via power query for the source to automatically point to the current year folder without me having to do the manual step? Let me know if you'd like more details. Thanks in advance for your help.

[MFelix · Accepted solution]

Hi @Anonymous ,

This can be achieve by the use of parameters check the video below with a full explanation, you don't need to create an excel spreadsheet with the path information but you can use an alternate version by doing the following code in the source:

"C:\folder\folder" & Parameter_Name & "\Folder\Folder\"

Then you just need to choose the parameter to adjust your PBIX file.

[PaulDBrown]

It sounds like a task which Power Automate/ Flow is capable of doing with some tweaks. (Not that I a know much about Power Automate/Flow)

[kentyler]

You can't do this directly....:) but here is a link to a RADCAD post that gives step by step instructions for doing it in M in the query editor.

https://radacad.com/fetch-files-andor-folders-with-filtering-and-masking-power-query

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.