I wanted to get data in Excel via a WebRequest and use in the Web Request Url a parameter defined in Excel in a Named Cell.
It took me some time to set this up and I share here my learnings.
Problem description
I want to get data in Excel via a WebRequest and use in the Web Request Url a parameter defined in Excel in a Named Cell.
I want the user not to have to open PowerQuery to set the Url parameter but have it directly visible in the Excel Configuration Sheet. Moreover I don't want to duplicate parameters in Excel and PowerQuery.
PowerQuery Code to get Excel named cell value
The PowerQuery code to get the value of a named cell in Excel looks like this:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| (CellName as text) => | |
| let | |
| Source = Excel.CurrentWorkbook(){[Name=CellName]}[Content], | |
| Value = Source{0}[Column1] | |
| in | |
| Value |
Configure the WebRequest
The best way to use the Parameter in the WebRequest is to go directly to the Advanced Editor
See an example below how to connect to a Jira WebRequest; the first two lines build the WebRequest using the parameters defined in Excel via a named Cell:
