...taking control of an Excel Power Web Query is another thing entirely

This post is the follow up to Getting XBRL in Excel is dead easy with Power Query.

Now to gain control of a "legacy" web query, to dynamically add your own variable parameters, say input a ticker into an X Sheet, you had to feed the query into Excel via an iqy file (internet query - this tech was actually introduced by Microsoft way back in the last millennium).

You no longer have to mess around with peculiar files in Get & Transform but in a lot of respects its way more complicated. Because Power Query is about Tables, and its about Tables; did I mention its about Tables? Power Query thinks in Tables, all it outputs is Tables* and what you input better refer to a Table somewhere along the line or there will be no output.

*Well strictly speaking you can specify that it outputs part of a table so you can squeeze a solitary value out of it.

So you need to know about Excel Tables and you probably need to know a little bit of Power Query M (The query language that powers Get & Transform). There is some good news, you don't need to learn all of this if you don't want to, as we've done it for you, and as always, in the name of transparency, we will try to explain along the way what we've done and how its doing it. And maybe throw in the odd video as well.

Moving to Tables is mostly a good thing. It encourages good practice in the way you treat and organize your data but it does mean you are entering the realms and rigidity of database structures (sort of Microsoft Access by the back door!). But it does mean (at least while you've got your head stuck in a query) you have to leave behind some of the lovely freedoms of Excel. The quaint idea that a cell could be any old data type depending on what turned up doesn't wash in a Table.

There are three ways of connecting a dynamic variable, each slightly more complicated and they are all demonstrated in this example Excel Workbook:

1. Put your dynamic variable in a Table. A Table! Now with a Table, Power Query can potentially see this variable and so we can edit our original query to reference it.

2. Create a new query to reference our new Table and and edit our original query to reference this new query. Cleaner solution and if we need to transform our variable, we have the space to do so in this new query. Also we now have a dynamic variable query that we can re-use.

3. Even more editing to turn our second query into a Function. This is what we do in the X Sheet.

Whichever option you choose, you are going to come face to face with the Power Query M Language. With the third option, you will also need to enter the Advanced Editor.

Now I'm not gonna run through each stage of the process of creating the above three solutions because I'll be here all day but we have videos to show you exactly how to achieve each result. I will though highlight here the key components in each approach and any other useful observations.

1. Simple Table reference. Watch the video.

Once you have created your new table, you need to replace the static variable in the original web request with this:

Excel.CurrentWorkbook(){[Name="TableName"]}[Content]{0}[ColumnName]

So the key line in your original web query will look something like this depending on what you named your Table and its solitary Column:

Source = Web.Page(Web.Contents("http://xbrlsheet.xbrlxl.com/?tkn=0&eid=" & Excel.CurrentWorkbook(){[Name="Company"]}[Content]{0}[Ticker])),

NOTE: Your variable doesn't have to be in an Table. You can just use a Named Range. It works because a Named Range is in fact a pseudo table. What you have to remember is that a Named Range has no column name so it enforces a default column name on it. So your ColumnName in this circumstance is Column1.

2. New query to reference our new variable Table. Best to watch the video.

You can easily make a query out of an existing Table in a sheet via Get & Transform. Tables are very keen to define each Column in a Table as a specific type. We need to change the Changed Type Applied Step to:

Table.TransformColumnTypes(Source,{{"Token", type text}}){0}[Token]

This also insures that we return a value rather than the whole table by adding the "{0}[Token]" 1st cell reference to the end.

The main query can now reference this new query which we called Authenticate in the video:

Source = Web.Page(Web.Contents("http://xbrlsheet.xbrlxl.com/?tkn=" & Authenticate & "&eid=" & Excel.CurrentWorkbook(){[Name="Company"]}[Content]{0}[Ticker]

In the video we actually managed to make all the changes without having to tussle with the Power M Query Language in the Advanced Editor.

Best to save/convert the query to a Connection Only query. Otherwise you'll end up with a new unwanted Table in a new sheet every time you exit the Query Editor. You need to find the "Load To..." option to do this.

WARNING: If you are on the default (strictest) Privacy setting, the main query won't run and you will get a message something like this:

Formula.Firewall: Information about a data source is required.

or:

...references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Excel, rather pathetically, can't distinguish between a local (our new variable Table) and an outside source as this juncture. By converting this query to a Function, as we do as the next option, resolves this piece of stupidity. We can of course also set our Privacy level either at the Workbook level or in  the Query Settings in the Query Editor (File>Options and Settings>Query Options>Privacy) to "We don't care" (Always Ignore Privacy Level Settings).

3. Turn our new query into a Function. And check out this video if you want to beef up the power!

This is our favorite option, the power Power Query option but you do have to dip your toe into the Advanced Query editor.

It saves the query from trying to create a new table in Excel every time unless you make it Connection Only and circumvents the mindless security objections as described above. You can also, rather usefully, opt to pass a variable to a Function as we do in the X Sheet. Its like proper programming! These advantages are spelled out in more detail in a post on my other blog - Jot About Excel. And again of course the Function you build is re-usable in other queries.

And in reality the changes are fairly simple. Top and tail your query with these two lines:

let Years = (YearNo as number) =>

in Years

where Years is what we named our new Function and YearNo is the variable we pass it (just use empty brackets if no variable is required). As you can see, if you are familiar with Lambda expressions, its uses a similar syntax.