When you walk down a few months in the Power Query lane, you’d feel if only there was a way to make the File Path (i.e. Source Step) in Power Query Dynamic, your queries would be so much better if their file source is dynamic. The answer.. YES there is way! In this post I’ll share, how can you create a dynamic folder or a file path, both in Excel Power Query and in Power BI.   Grab the Video first?   Dynamic Folder Path in Excel Power Query Quick heads-up, this technique is meant for gathering data from files or folders in your computer. It’ll come handy when you share your Query (Excel / Power BI file) and the source data with others, the file path will update automatically in their computer too.   Consider I have a “Files” Folder on my Desktop with 3 Excel files to be combined into a Single one. I’d like the folder path to be dynamic, so when I change the folder location my query should update the new path automatically.   Step 1 – I add a Blank Excel File to the Folder (you can name it anything). Note – It is essential to keep our working file (Consolidate All Data) in the same folder location as the other files.   Step 2 – I write a quick formula in our working file (Consolidate All Data) to retrieve the current file path. And this formula would update automatically when the folder location is changed 😎 =LEFT(CELL(“filename”),SEARCH(“\[“,CELL(“filename”))) Confirming the above formula returns the correct folder path   Step 3 – Now I need to do a couple of things before I work in Power Query. Convert this into table, use Ctrl+T. Column header as “Path” and table name “DynamicPath” Our table has only one row and one column and it should like this..   Step 4 – Take this “DynamicPath” Table to Power Query. From the Data Tab >> From Table Range. Right click on 1st Row and Select Drill Down Doing this will convert the path into a Text (Variable) Once you do that, the query should look like this..   Step 5 – Open the Advanced Editor from the View Tab and let’s work further on this. After you drilled down, the second step is named as “Path” I’ll add a simple one line M-code in the advanced editor let   Source = Excel.CurrentWorkbook(){[Name = “DynamicPath”]}[Content],   Path = Source{0}[Path],   GetFolderFiles = Folder.Files(Path) in   GetFolderFiles The highlighted code is one that I have added. Appears like this..   Result – After you commit to the above code, the results seem perfect. Just remember to filter out our working file (Consolidate All Data) from list, else you’ll have data duplication. Now when you change the path for the “Files” Folder the query won’t break.   Dynamic File Path in Excel Power Query You may have the need to create a Dynamic File path rather than a Folder path. In that case we can modify on the same query to get that done. In the Advanced Editor I create modified code. let   Source = Excel.CurrentWorkbook(){[Name = “DynamicPath”]}[Content],   Path = Source{0}[Path] ,   GetFiles = Excel.Workbook(File.Contents(Path & “Year – 2005.xlsx”)) in   GetFiles If you modify the old code with the highlighted bit, you’d rather pick up a single file from that folder i.e. Year 2005 Excel Workbook instead of all files in that folder. See the results, we now get all the sheets from the selected Excel file.   Dynamic File Path in Power BI Unfortunately, in Power BI a dynamic folder / file path not possible. Because unlike Excel, you don’t get the ability to write a formula in a cell that returns a file path. 🙁 But we can still create a workaround that suffices as a temporary fix. Assume this Power BI model with multiple queries. Here, I am assuming that the data for all queries is coming from a single folder (but it may have sub folders).   Step 1 – In Power Query Editor >> Home Tab >> Manage Parameters >> Create a New Parameter In the parameter window name the parameter as “Path” and paste the folder path in the Current Value. The Idea is to create a “Path” Parameter and pass it though the source step of all the queries.   Step 2 – Now in the source step of each query add the “Path” parameter. Below picture shows how can you use the “Path” parameter when getting files from a folder. The code will slightly change in case you’d like to get the data from an Excel file instead. let   Source = Excel.Workbook(File.Contents(Path & “\Data.xlsx”)) in   Source Note that I prefixed a back-slash “\” with the file name “Data”, that is important to get the path correct.   This method in Power BI although not fully automated, will involve changing the file path only once in the parameter and that would change all the queries automatically.     More Power with Power Query Unstack Columns in Separate Rows Change Dates from MM-DD to DD-MM Format Dynamic Column in Split By Feature Replace Errors in Multiple Columns at once Dynamic Column Selection Dynamic Column Renaming Running Total in Power Query   .        Automate repetitive data cleaning tasks using Power Query A comprehensive course to learn Power Query to automate all your mundane and repetitive data cleaning tasks in Excel or in Power BI   DOWNLOAD THE COURSE OUTLINE    |    ENROLL IN THE COURSE