- #Dynamic web queries excel 2016 how to
- #Dynamic web queries excel 2016 update
- #Dynamic web queries excel 2016 manual
- #Dynamic web queries excel 2016 code
- #Dynamic web queries excel 2016 plus
Replace this: Source = Excel.Workbook(File.Contents( "C:\Users\marks\OneDrive\Documents\SourceFile.xlsx"), null, true), Use named ranges as sourceįinally, we need to insert the FilePath and FileName steps into the Source step. In this example, we have two named ranges, but we can create as many as we need.
#Dynamic web queries excel 2016 code
REMEMBER: Make sure each line of code in the Advanced Editor is finished with a comma. We will add new steps directly after the let statement.įilePath = Excel.CurrentWorkbook() : Uses the first row and the first column from the table of the named range 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.
#Dynamic web queries excel 2016 plus
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.
#Dynamic web queries excel 2016 manual
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).
#Dynamic web queries excel 2016 how to
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.
#Dynamic web queries excel 2016 update
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.