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.

Jumping the tabs

Going for the record of the shortest post...

If have loads of tabs and you need to jump quickly from one end of the workbook to the next, hold down the control button when clicking on the direction arrows next to the tabs at the bottom of Excel.

An Indirect reference to a named Dynamic Range doesn't work

Excel doesn't like you getting too clever clever. There's only so much dynamism she'll take.

I use the Indirect function quite a lot where I want the flexibility to expand a model for more and more cases. So I need to copy existing formulas to work for the next case and if the cell references are indirect I don't have to update these each time I or my users copy formulas.

If you make an Indirect reference to a simple static Named range reference, then it all works fine as Excel performs her usual magic.

If you make an Indirect reference to a dynamic Named Range where the range is dependant on the result of a formula, usually an Offset formula with a CountA thrown in, then you'll be out of luck and scratching your head as to why you are left staring at a REF!. Dunno why she objects. I'm sure there's a good reason. What's a bit annoying is she never tells you the dynamic Named Range is the problem. Hence why I'm jotting it down here.

So is there a way round it? Well you can use a different sort of dynamic named range, a more formal sort, an Excel Table.

It can have the same name and so does the same trick. They both play nicely with the Index function allowing you to dynamically reference any value in these ranges. The advantage of a table is you can reference its column by name.

=Index(SomeTable[ColName], 1)   -   gets the 1st cell in the ColName column

You don't have to specify the column name of course. This works for both Tables and Named Ranges and may suit a more dynamic approach.

=Index(SomeTable, 1, 1)   -   gets the 1st cell in the 1st column.

You can also, like with a Table, specify just a column.

=Index(SomeNamedRange, ,1) - sets up a reference for the entire 1st column by omitting the row.

Of course in the same scenario with a Table, you don't even need the index function!

Can't understand a ridiculously long formula?

Hey I never knew this until I read it in this blog after referencing a post by the same author in my last blog.....just highlight each bit of the formula you don't understand and press F9 and it will evaluate it for you. Undo it (Ctrl + Z) and move onto the next bit or maybe a smaller bit if you still don't understand the value its giving you.

Remarkably handy as even I don't understand what half the formulas do in my Xbrl sheets. I'd love to make them simpler but the trade off is more columns and sheets to track or worse macros ,where whats going becomes even more opaque.

Talking of macros, its kinda like using Shift + F9 to evaluate an expression when debugging one.

At least in formulas the logic is on display for all to see. And now you don't have to bust a brain cell to work out what each bit is doing. You can just press F9 and see.

Beware cut and paste is not the same as copy and paste

Of course its not! But you might think it was if you then delete the cell you just copied. Wrong! If you move the cell via a cut or a drag, it will drag any references in formulas to the cell with it. Sometimes this is actually what you want to happen but a lot of the time its not. And you won't now anything about it! Until you open your sheet weeks later and wonder why your formulas are riddled with Ref! errors.

You may already be aware of this through bitter experience or you may have read one of the many warnings posted on the web. This article is a particularly good explanation of the what and the why.

So if in doubt, always copy and paste then delete the original reference. If you are using our TotaliZd product (coming soon), then be careful how you move your tags around. Copy & Paste!

Turning the Tables

So finally I've turned to Excel Tables. With Microsoft putting Get & Transform centre stage in their admirable efforts to establish Excel as data mashup central, I've had to succumb to their wily charms.

Tables can be a law unto themselves! You're never quite sure what they are going to do until you start playing with them.

So hear are a few things I think I've figured out:

Lesson 1: You can add columns to the front or end of your Power Query Tables and refresh the data (from your chosen data source. If you wanted it to be XBRL - you could check out our XBRL XL) and your new columns will stay the same. Both the columns and the data in them will remain and we've taken advantage of this in totaliZd (coming soon). Note you don't even have to "officially" resize the Table - start typing to the side of the Table and it will immediately be consumed into it!

Lesson 2: You can add rows to the bottom of your table and they will disappear when you refresh the data source. Bum! We had to bear this in mind in totaliZd!

Lesson 3; When you add a formula to one of your new columns that references a column coming from the data source, it appears in all the other rows. Magic!

Lesson 4: What if I want the formula only to exist in certain rows,say row subtotals. A proper sort of database like table shouldn't have subtotals of course but we're mashing up here so maybe you do.

Well you can just the delete the formulas in the rows you don't want. And no all the formulas in all the rows will not magically disappear when you do this. Phew! If we're talking a few subtotals here and there then you can delete all the formulas bar one and then copy that to the minuscule number of cells you do want to have formulas. If you are unnerved by all this Table magic, you can simply tell Excel to stop casting spells. Not with a wand but by changing the proofing options under settings to clear the Fill formulas in tables.

Lesson 5: Now I know I said if you insert a row, you will lose it on refresh (Have I not learnt anything from Lession 4!?). Well there might be times when you want to do it anyway (especially if you are dealing with a Table that wasn't created from a Data Query - it is also an option in totaliZd in one special circumstance where you need to create a total that ought to be but isn't there).

Excel has a special way of enabling you to do this within a Table that is not immediately obvious. It is not up on the Table Ribbon and you can't insert it like you might insert cells elsewhere but you can right click on the cell in the Table where you want the row (or column for that matter) and make a wish, sorry click on insert and the option will appear. Be aware that if you refresh the data after inserting a column in the middle, that WILL disappear. Its not a wizard!

Now, because I have painfully learnt Lession 4, you might want to copy that row somewhere else as well if you are planning a refresh, maybe two rows below the Table but not one row below, otherwise it will eaten by the Table and will be banished to oblivion upon refresh. Like a bad witch, Excel Tables can sometimes be malicious.

On Error Resume Next

There are alot of posts out there telling you to stay away from:

On Error Resume Next

But I say for simple macros, its the quick and incredibly easy way to stop macros behaving weirdly when someone does something (or uses it somewhere) unexpectedly. When you want to code a quick macro, you don't want to be worrying about sophisticated error trapping or coding for all scenarios. Because then its not quick. So just stick this simple line before any dodgy VBA calls and you or your users won't be faced with unexpected behaviours or subscripts out of range.

Sub FindValue()
Dim srchVal as String
Dim colHeading as String
Dim srchRange As Range
Dim foundRange As Range

srchVal = ActiveCell.Value
colHeading = Cells(1, ActiveCell.Column).Value

On Error Resume Next
ThisWorkbook.Sheets(colHeading).Activate
Set srchRange = ActiveSheet.Cells
Set foundRange = srchRange.Find(srchVal, , , xlWhole)
foundRange.Activate
End Sub

This macro searches for the value in the current cell in the sheet named at the top of its column. If there is no sheet name at the top of the column, it does weird things. But not with:

On Error Resume Next

It moves to the next line and sets the current sheet as the active sheet. So, simply and neatly, you just end up where you started. Of course its worth having a quick look at what that resumed next line of code could be coz sometimes it could end up taking you someplace weirder. And you could write lots of code to make sure this never happens, But really, its a macro - a quick and dirty way to get something repetitive done fast.