About Jeff Robson

I take clients' raw data, analyse it & use it to provide amazing insights that help them make better decisions. I use Microsoft's world-leading Power BI & Excel products to provide financial modelling, data analysis, dashboards, reporting, & training. I consult to CFOs and business leaders in medium-large clients across a broad range of industries such as oil & gas, mining, property, agribusiness, finance, health, manufacturing & professional services. When I'm not consulting, I also run training courses across Australia/NZ and around the world.

Power Query: Unpivot using Column Numbers rather than Names

by Jeff Robson Say my name, say my name When you're working with data from Excel in Power Query, column names can often change, new columns can be added and columns can be deleted. Excel users don't think twice about doing any of these things as they all seem quite normal and logical however they [...]

2021-06-25T10:46:17+08:00Power Query|

Fixed vs Variable Loan Interest Calculator

by Jeff Robson Fixed vs Variable Loan Products ... which is best? It can be difficult to compare fixed vs variable loan products. Banks often market loans with fixed interest rates using the selling point of certainty: "you'll always know what your repayments will be". Check the fine print! While certainty can be somewhat attractive, [...]

2021-06-04T10:40:48+08:00Arrays, Office 365|

Tricking Non-Dynamic Array Formulas into Spilling

Tricking Non-Dynamic Array Formulas into Spilling by Jeff Robson   Tricking non-dynamic array formulas into spilling isn't something you'd naturally think of doing. Dynamic Arrays are great but sometimes the formulas that refer to them behave in unexpected ways, and sometimes these don't spill ... without any obvious reason. For example, the DATE function spills, [...]

2021-04-29T10:37:25+08:00Arrays, Functions|

Corkscrew Calculations using Dynamic Array Functions

by Jeff Robson Corkscrew Calculations using Dynamic Array Functions If you've come to this page expecting information about how to open your wine bottles, you're going to be very disappointed!  Sorry! The corkscrew calculations we're talking about here are those you'd use when calculating a balance sheet account in a financial model. Accounts Receivable Example [...]

Financial Modelling using Dynamic Array Functions – no Copying & Pasting!

by Jeff Robson Financial Modelling using Dynamic Array Functions Best practice financial modelling has always been to enter your formulas in blocks: enter a formula, then copy this across and possibly down also, making sure you have your absolute and relative references set correctly. This is very useful because it is faster to build a [...]

Read a Range Name, Table or Sheet from all Excel files in a Folder using Power Query

If you have a bunch of Excel files that all have the same structure, you can easily pick a table name, sheet name or range name that exists in all of them and read that one item from all workbooks into a single table using Power Query. It's an incredibly useful tool for situations such [...]

Power BI Data Sources for Estimating Market Share

Power BI data sources can be almost any file, database or web service. Here's an innovative way to use this amazing connectivity to estimate your market share using free information sources from the web. Facebook tells me my Market Size If you've ever placed an ad on Facebook, you'll know the incredible range of characteristics [...]

2021-07-27T15:29:40+08:00Data Handling, Power BI|

Fix MYOB Account Numbers Exported to Excel

Fix MYOB Account Numbers Exported to Excel It used to be simple to export data from MYOB AccountRight to Excel.  With more recent versions, the export has changed which means all your formulas looking for particular account numbers won't find them.  Here's how you can fix MYOB account numbers. The Cause When MYOB [...]

7 Mischievous Adventures in Excel

This eBook has some great ideas on how to have more fun in Excel than you can poke a kitten at (usually at your colleague’s expense!) *evil laugh* If you’d like some ideas for some mischievous adventures that will mess with your friend’s head (and their models), download this now! You'll learn things [...]

2017-03-08T03:53:54+08:00Keyboard, Productivity|

Excel Shortcuts: 22 of the very best and most handy!

A collection of our favourite keyboard shortcuts in Excel. Pin it up on your wall & share it with your friends. Shortcuts to save time and maximise productivity including: Creating charts faster Faster formatting Navigating a large spreadsheet and 19 more!        

2018-09-03T12:23:14+08:00Keyboard, Productivity|