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.