Three reasons why you should use Functions to retrieve dynamic parameters for Power Web Queries

Rather than straight forward queries (or direct references to cell variables encapsulated in worksheet Tables), here are three reasons why you should use Functions to retrieve dynamic parameters for Power Web Queries.

1. If you convert a query pretty sharpish into a Function, it won't litter your workbook with needless invoked Tables when you close the Query Editor.

2. You won't full foul of the petty privacy settings. Excel assumes every query in a query (query that references another query) is a security risk even if said query is simply referencing a cell in the same workbook.

You will get this sort of message:

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

You will need to set Privacy Settings to "Always Ignore Privacy Level Settings" to circumvent this alleged error, thus completely undoing Excel's ham-fisted attempt at security. Or turn the referenced query into a Function.

3. You can pass a variable to a Function giving you greater flexibility. So with the X Sheet we usually want different parameters for different XBRL SEC filings so we can pass different parameters to the underlying web query by passing a filing number to the Function.

You can see how to create a Function to pass dynamic variables from an Excel Cell to a Power Query in my other blog. We use a web query as an example to pull back XBRL financials but it could equally be referencing an API Web Service or Database.