Please note javascript is required for full website functionality.


Power Query: Custom Built Functions

13 September 2017

Last week, I looked at how to access and test the built-in Power Query functions. There are occasions, however, when I am repeatedly using the same logic, and I want to store it in a custom function of my own. Let’s take a look....

ModelOff Sydney

11 September 2017

This week in Sydney, ModelOff hosted their Global Training Camp, featuring speakers from Microsoft and several other companies, including our very own Tim Heng.

Weird Little Traps: Excel Going Wrong from Day 1…

8 September 2017

While our VBA blog series takes a little hiatus (it’s coming back soon, we promise!), we thought we’d share with you a problem that raises its ugly little head from time to time.

Power Query: a Function Function

6 September 2017

My blog comes of age this week as it hits the big 4-0! I am feeling quite empowered, so that gives me an idea! In M-Powered, I described how to get to a description of the functions that can be used in M code to manipulate data by accessing the Microsoft help pages. There is however another way to get to the functions. Let me show you....

A to Z of Excel Functions: The COMBINA Function

4 September 2017

This function returns the number of combinations (with repetitions) for a given number of items.

A to Z of Excel Functions: The COMBIN Function

1 September 2017

Are you often COMBIN the Excel functions to see how many subsets you can make? This function returns the number of combinations for a given number of items (i.e. the number of distinct subsets of items where order is unimportant). You should use COMBIN to determine the total possible number of groups for a given number of items.

Power Query: Null and Not Void

30 August 2017

In Pivotal Pivoting, I showed how easy it was to pivot columns. When I pivoted my expense code column to show the amounts for each expense code, I had a table with lots of null values in it, as shown below. I am going to use this table to show how I can sum columns where some values are null...

Monday Morning Mulling: August Challenge

28 August 2017

Last Friday, we posed perhaps one of the hardest challenges that we’ve had so far: can you create a file that will refresh your Power Query queries, and pop up a message box letting us know when it’s finished?

Final Friday Fix: August Challenge

25 August 2017

Welcome to this month’s Final Friday Fix! The inspiration for this month’s challenge comes from an actual problem that we’ve been trying to solve for some client work, that we couldn’t find an answer to anywhere online. But first, let’s talk about refreshing queries – that is, Power Query queries in particular. ...

Power Query: Structured Columns Deconstructed

23 August 2017

In Power Query, explore the ‘Structured Column’ section on the ‘Transform’ tab, which is not available for most column types.