Creating a Banding function in Power Query

I got a question on the blog recently about creating a banding function in Power Query, or creating buckets for Accounts Receivable transactions.  (30-60 days, 60-90 days, etc..)  As this is something that can be applied to a lot of areas, I thought it might make a good post to cover.

If you'd like a copy of the sample workbook, you can find that here.

 The need for a Banding function

Picture that you have a list of transactions that could be from 1 – 170 days overdue, and you'd like to group them as follows:

  • 0-30 days (current)
  • 31-60 days
  • 61-90 days
  • 91-120 days
  • >120 days

You could create a table with 365 days in column 1 and the appropriate description in column 2, then merge them, but that seems like a lot of work.  It would be much easier to create a simple little function that banded them correctly for us.  Especially if you happen to have a little template that you can refer to…

The Banding function

The banding function template we need is shown below:

image

Notice the key parts here:

  • days (highlighted in yellow) is the variable that we'll pass into our function to evaluate
  • ARBand is the name of our function
  • Between the indented curly braces we have a list of the potential outcomes we'd like to use for our bands.  If the value of x (which we will test) is less than 31, it is labelled "Current".  If not, then -- if it's less than 61 -- it is labelled "30-60 Days" and so on.  The final clause (=>true) basically returns an "else" statement.
  • The Result line then checks the days variable against the list and returns the correct match or the "else" clause if no match is found (">120 Days" in our case)

This banding function is a super useful template that you can modify to suit for any grouping needs.  If you are updating this function for your own scenario, make sure that the yellow pieces match, the orange pieces match, then change the number bands and offsetting text pairs (ensuring that the remain wrapped in quotes.)

You can add as many steps (bands) as you need, just make sure that each line ends with a comma, and the =>true line stays at the end of the list.

To implement the function:

  • Create a new query –> from blank query
  • Enter the Advanced Editor
  • Paste in the code shown above
  • Modify your bands to suit
  • Click OK to exit the advanced editor
  • Name the function

I obviously didn't need to edit mine, and I called mine "DayBanding".

Setting up the data

There are two pieces that I need to deal with for my scenario.  I have a transactions table, but it only lists the original transaction dates.  In order to work out the day bands, I need to create a way to show how many days have been elapsed.  Easy enough to do, I just need to pull in today's date from somewhere.

So I created a simple table that holds today's date:.  (It's hard coded in the same file, since the transaction dates are hard coded as well.)  Regardless, it looks like this.

image

And here is an excerpt from the table of transactions:

SNAGHTML5829669

Grabbing today's date

Since I'm going to need the date to work out the number of days outstanding, I'll start there.  The steps to accomplish this:

  • Select a cell in the parameter table –> New Query –> From Table
  • Rename the query to "Today"
  • Click the fx icon in the formula bar
  • Modify the formula to show as follows:
    • = Date.From(#"Changed Type"[Value]{0})

(I've discussed this technique a lot on the blog in the past – like in this post – but it basically we are drilling in to the first item in the [Value] column of that table, then wrapping the item with the Date.From() function to extract the date.  We'll use this shortly, but first…

  • Go to home –> Close & Load To… –> Only create connection

And we now have a way to pull up the date when need.

Grabbing the transactions table

Next I needed to pull in the ARTransactions table, include the date, work out the number of days outstanding, then band it all.  Here's the steps I used:

  • Select a cell in the ARTransactions table –> New Query –> From Table
  • Add a Custom Column
    • Name:  Today
    • Formula:  =Today

This works since we called our original function Today, and we drilled right in to the date.

SNAGHTML594999e

Next up, I needed to subtract the Transaction Date from Today's Date:

  • Select the Today's Date column
  • Hold down CTRL and select the Transaction Date column
  • Go to Add Column –> Date –> Subtract Days

SNAGHTML5972c5f

Using the Banding function

The final step is to call the banding function and classify our days:

  • Add Column –> Custom Column
    • Name:  Day OS
    • Formula:  =DayBanding([DateDifference])
  • Right click the Today's Date column –> remove

And we have a nice table that has the grouping level we need:

SNAGHTML59a3950

Another little trick…

Now I'd like to build a Pivot Table using this, but I'm not really in love with the idea that I have to load this data to a table first.  I mean really, I only added a single column.  Normally I'd load this to the data model, but I don't really need Power Pivot for what I want to do.  So let's take a look at another little trick that will let us avoid the data duplication that would be caused by loading this to either the Data Model or the Worksheet.

  • Close & Load To… –> Only Create Connection

Now we need to build the Pivot Table.  I'm going to show the steps for this in Excel 2016 (because I'm working on a computer that only has Excel 2016), but you should be able to make this work in Excel 2010/2013 as well.

  • Insert –> Pivot Table
  • Choose External Data Source (yes, you read that right) –> Choose Connection

In this window, your queries should show up!

image

  • Select the Query – ARTransactions –> Open
  • Choose to place your Pivot Table wherever you'd like it –> OK

Configure the Pivot Table as follows:

  • Rows:  Customer
  • Columns:  Days OS
  • Values:  Amount

And with a couple of sorting and formatting changes, I've got this thing of beauty:

image

Final Thoughts

I showed a couple of tricks here:  How to use a Banding function, and how to build a Pivot Table directly against a connection only query without having to go through Power Pivot.  Both useful things that you should have in your arsenal of tools.  Smile

Last Chance to register for Excel Summit South

Excel Summit South 2016

I’m only a few days away from my flight to New Zealand to kick off the first leg of Excel Summit South.  I’m really looking forward to it.  And if you’ve been sitting on the fence as to why you should attend… just ask Jeff Weir.  (Seriously, read his post, it’s awesome!)  But you need to act quick here, as it’s pretty much your last chance to register for Excel Summit South now.

What it’s all about

This will be a great opportunity to keep up with modeling practices, extend your analysis skills, and see what’s happening with Excel.  Full details about the Summit can be found at the Excel Summit South 2016 web page, but you can read about some of the high points below, or – did I mention that you should read Jeff Weir’s Why I’m going to Excel Summit South. (And why you should too) post on Daily Dose of Excel?

When and where is Excel Summit South?

The Summit will take place at these cities on the dates shown:

  • Auckland: Thurs-Fri 3-4 March (register by 28 February)
  • Sydney: Mon-Tues 7-8 March (register by 1 March)
  • Melbourne: Thurs-Fri 10-11 March (register by 6 March)

Last Chance to register for Excel Summit South - Discounts available!

As an additional incentive, we’ve arranged a last chance registration discount, but only up to the date above.  Simply REGISTER HERE and use the code LASTCHANCE to save 30% on your registration fees.

23 Excel Master Classes

With your registration, you can choose from 23 master class sessions over two days.  There are twin tracks for modelers and analysts alike, and you can jump between if you’d prefer to do so.

Modeling Track – Manage Spreadsheet Chaos, Testing Spreadsheets, Avoiding Common Errors, Modeling Best Practices, Simulation Analysis Without VBA, Power Pivot.

Analysis Track – Tables, Pivot Tables, Power Query, Data Visualization, Dashboards, Automating Excel.

The Who’s Who of Excel…

Learn from six (seven!) leading Excel MVPs as they discuss the Excel topics most useful to you.

Liam Bastick (AU), Zack Barresse (US), Bill “Mr Excel” Jelen (US), Ken Puls (CA), Jon Peltier (US), Charles Williams (UK), with a guest appearance by Ingeborg Hawighorst (NZ) in Auckland.

Hear industry leading speakers about Financial Modeling best practices, standards and spreadsheet risk.

Smita Baliga (PwC), Félienne Hermans (Delft U), Ian Bennett (PwC), Andrew Berkley (F1F9).

Interact with members of the Microsoft Excel Dev Team as you explore with them the future of Excel.

Ben Rampson and Carlos Otero from the Microsoft Excel product team.

Network and Interact

As if the classes weren’t enough, we’ll also have Panel Discussions, Ask The Experts sessions, Demonstrations of Commercial Excel Tools, and even an Evening Meet-up where you can ask your Excel questions over a beer.  (Full caveat… the quality of the answers may decline as the evening progresses!)

A shout out to our principal sponsor

PwCOur principal sponsor for this Summit is PwC Australia and PwC New Zealand.  We appreciate them coming on board to host this event!

Excel 2016 Updates

I was a bit surprised to see some Excel 2016 updates when I opened it up this morning.  For reference, I am on an Office 365 early release program – so I might get these a bit before you do – but how cool is this? Some of the key ones that made me take note:

New Formulas

We’ve got some new formulas to add to our arsenal.  I haven’t tried any of them yet, but the ones listed were:

  • CONCAT
  • TEXTJOIN
  • IFS
  • SWITCH

Chris Webb just posted a blog on the first two, IFS sounds useful, but SWITCH… Are you kidding me?

image

I LOVE that function in Power Pivot and am just itching for an excuse to use this one in a real world Excel project.

A New Chart Type

When Excel 2016 first came out, we saw some new chart types added to the product for the first time in… ages.  Those included:

  • Treemap
  • Sunburst
  • Histogram
  • Waterfall

And now we got another for those of us on the subscription:

  • Funnel Charts

This is a pretty simple one, but here’s a sample mocked up in about 3 seconds:

image

A Power Query (Get & Transform) Update

I put this last, but to me this is the biggest deal of the whole bunch.  The Power Query engine has been updated to version 2.29.4217.xxx.  It’s hard to see what’s been added, as the update hasn’t been released for Excel 2010/2013 yet, nor has a detailed feature page…

Having said that, a feature that I asked for a while back has finally been implemented:  Monospaced Fonts.

The importance of this is huge.  Power Query has always been big on using a pretty font, which wasn’t monospaced.  I.e. the characters weren’t the same width.  This is a big problem if you are trying to split by number of characters, as they just don’t line up.

Now, there is still an issue… Power Query is still aggressively trimming spaces (something that started with version 2.28.xxx) as you can see below:

SNAGHTMLdcb06b0

But, if you go to the Advanced tab and click the new Monospaced option, you get this beautiful view:

SNAGHTMLdcbe2f6

How much easier will that be for splitting columns based on width?  Like 1000% easier, that’s how much!

 Dear Power Query team

This is a fantastic feature, thank you.  I’ve got two asks for you:

  1. Can you get us the update for Excel 2010/2013 fairly soon?  We need this there as well.
  2. Can you please give me an option to set Monospaced as the default way to display my queries?  This is not due to the overzealous trimming issue (which I do want to see fixed) but rather because this is the way I need to see my data come in every time.

Thanks!

More about Excel 2016 Updates

If you want to see Microsoft’s official page listing all the new features in this Office update, or if you’d like to get into their early release program, have a read here: https://support.office.com/en-us/article/What-s-New-and-Improved-in-Office-2016-for-Office-365-95c8d81d-08ba-42c1-914f-bca4603e1426?ui=en-US&rs=en-US&ad=US

Excel Summit South

Yes, you read that right.  If you haven't heard yet, I'll be coming to New Zealand and Australia in just under a month!  And the entire purpose of the trip is to come and share Excel knowledge for with my friends and colleagues south of the Equator.

I'm pretty jazzed about this, and not just because I get to go to the southern hemisphere for the first time in my life.  And also not just because I get to talk about Excel when I'm there.  That would be enough, but no… I'm jazzed because I get to do this with some pretty cool friends who are world respected leaders in their area.

Excel Summit South

The main purpose for my trip is the Excel Summit South conference.  Two days, two tracks of advanced Excel material in 3 different cities:

  • Mar 3&4: Auckland, New Zealand
  • Mar 6&7: Syndey, Australia
  • Mar 9&10: Melbourne, Australia

And the best part about this conference is that – while it's sponsorsed by Price Waterhouse Coopers – regisration is open to everyone.  So basically, you can check the schedule, pick the sessions that interest you, and learn things that will impact your Excel skills.  In other words, if Valuation Modelling isn't your thing, then you can go to a Power Query class.  And if Power Query isn't your thing… well… you're kind of odd, but there will be something that is.  Smile

The cast and crew for this conference really can't be beat.  Charles Williams, Bill Jelen, Jon Peltier, Zack Barresse, Liam Bastick are all Excel MVP's on the bill (as well as Ingeborg Hawighorst in our New Zealand apperance.)  Heck, we've even got a couple of guys from Microsoft attending and presenting as well.  This is a fantastic opportunity to not only meet some of the big hitter independant Excel folks out there, but also to talk to Microsoft directly.  How can you pass that up?

My Sessions

If it hasn't shown yet, I'm seriously looking forward to this conference.  Personally I'll be leading two sessions:

An End to Manual Effort: The Power Query Effect

What Power Query is, why you care, and how it can re-shape and transform the data experience.  What's really special about this session is that I'm going to take this data and turn it over to Jon Peltier who is then going to take it and turn it into a dashboard.  This is perfect, as I'm demoing how to automate data cleanup, and Jon will show you how to use it to add true business value… the real life cycle of Excel data in just a couple of hours.

The Impact of Power Pivot

This one will be fascinating, especially for those who have never seen Power PIvot in action before.  In just an hour I'll show you how big business BI (business intelligence) is at the fingertips of anyone with an Excel Pro Plus license.  It's applicable to companies as small as one employee, and scales up to multi employee small businesses, and even large businesses.  (Departments in large corporations eat this up, as they effectively just act as a small business within the larger whole.)

Register for Excel Summit South now!

Tickets are going fast for this event in all cities, so we ecourage you to register sooner rather than later, and hope to see you there!  You can find out more details and register at:  https://excelsummitsouth.wordpress.com/