In the Power Query editor, click View -> Advanced Editor. Open the Queries and Connections pane by clicking Data -> Queries & Connections.ĭouble click the name of the query to open the Query Editor. Now we just need to use the named ranges in our query. So, make sure they form a valid file path. We will be combining these two named ranges in Power Query’s M code. Select cell C3 (the file path) and create a named range called FilePath. Next, we repeat the step above for the file path. To do this type the name of the named range into the name box and press Enter. Select cell C2 (the file name) and create a named range called FileName. NOTE: A common error is to forget the backslash at the end of the folder path. Source file – the data loaded into Power Queryĭownload the file: 0021 Power Query – Change source based on cell value.zip.Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference. I recommend you download the example file for this post. I must say “Thank You” to Celia Alves for sharing this technique on a recent online meetup.

What I’m about to show you is an even easier method. In that post, I show the manual method and a method using parameters. I have written about changing Power Query sources before ( Power Query: Change the Source Data Location). Wouldn’t it be better if the source could be linked to a cell value that we, or a client, could easily update? Yes, it would! So, in this post, we’re going to look at how to do that how to change the Power Query source based on a cell value. Or maybe you’re a consultant who creates solutions for others when you distribute the workbook to your client, you have to provide them with complicated instructions on how to update the data sources for their environment. This means the paths of all source files must be updated. If you’re like me, you build Power Query solutions in a test environment first then, when ready, it’s released into the wild.

Power query excel mac 2019