Working with small data sets

Intro:
Hello, and welcome to the CPA Australia Podcast, your weekly source for accounting education, career and leadership discussion.

Neale Blackwood CPA:
So in the November, 2020 article, I look at how we can use a little data table to capture statistics that might not appear in your larger data sets. Now, before we dive in, basically Excel is not a database, and it's actually been in the news lately with the Health Department in the UK, apparently used Excel to monitor and compile some statistics on COVID and it didn't end well, put it that way because Excel is not a database. So what I'm covering here is how you can use Excel to fill a gap. So Excel extracts from databases really well, but if you've got the situation where you might have some statistics that aren't in any databases, but you still need them to maybe finish off a report or finish off a dashboard. So Excel can be used to fill a gap there because you can create a table that is easy to input, and then we can use power query to convert it into a data layout.

Neale Blackwood CPA:
So the data that I've used in the example has a couple of columns, one for state, and then one for the metric or the statistic. And then the other columns in the table go across the page. And there's one column for each month, basically. So for each month you just add a new column, and you can go across the page to build up the report. Now, whilst that's really easy to enter data into, because all you have to do is basically add the date and then add the values as you're going down the sheet, it isn't very easy to use that data table in a report or a pivot table.

Neale Blackwood CPA:
So in Excel, it's worth spending the time to get the data laid out right. It's time well spent. Unstructured or badly structured data is not flexible. Now power query allows you to take a data layout that's really easy to enter data and convert it into a data set that's easy to report on in pivot tables, et cetera. And so that way you get the best of both worlds. Now well-structured data offers flexibility and also the ability to handle changes. Because it's almost guaranteed that after you create a report or a dashboard, you're going to get a request to get something slightly different in the report or the dashboard. And if the data is structured well, oftentimes that's a fairly easy request to answer, but if the data's badly structured, then it's sort of almost like, oh, we've got to start again because the data doesn't handle the report that you want to create. So well-structured data offers a lot of flexibility.

Neale Blackwood CPA:
Now in the old days, macros were used to do this conversion of, let's say an input table into a data table. And these days power query is much easier. There's no coding required. It's pretty much just clicking on existing options within power query to get what you need. Now, power query is built into Excel 2016 and later versions. It's also called get and transform, but pretty much everyone calls it power query. If you've got an earlier version, there are add-ins for Excel 2010 and Excel 2013. I have done a podcast on power query, so if you want a little more, more detail, you can go back to that.

Neale Blackwood CPA:
Now we need to what's called normalise this data, which means is of having a table that goes across the page, we want a table that basically goes vertically. So we're going to end up with two columns, one column for the state and one column for the statistic that we're measuring. And then we're going to have a month column and a value column. As I said, in the old days, you used to use a macro to do that and it wasn't easy to do, and you needed a little bit of coding, but nowadays you can do it very quickly in less than five minutes in power query. And once you do it in power query, it's captured. So if extra columns are added to the table or extra statistics are added to the table, you can automatically get that picked up with the power query. And if you want to see all of the steps and the parts of the power query and how you do it, the companion video is on the website with the article. So I'm not going to go into detail about how you do it in the podcast, more about why you do it and advantages with using power query.

Neale Blackwood CPA:
Now, once you've got that data set, then it's really easy to report with pivot tables, power pivot, and even power BI because power BI works very seamlessly with Excel. Power BI is totally separate to Excel by the way. So it is different to Excel. It's not an add-in or anything like that. It's a totally separate app. Now, if you make any changes to the input table, you do need to what's called refresh the query, which basically just means right clicking on the output table and using refresh. Now, pretty much if you add row or columns to the input table, the power query will pick them up. The exception to that is if you add another column that you'll want keep as what I call a static column. So in our example, we've got a state and we've got a statistic column or a metric column. Now they're the static columns. So they come across to the output table. In the video, I use the example of a manager. You might put the manager's name there. If you add a column that you want to keep, then you do need to go back to the power query and actually amend it. And again, I've shown you how to do that in the video that is on the articles website.

Neale Blackwood CPA:
So power query is a skill that I think all accountants should at least know about. And then a lot of accountants will find lots of uses for converting badly structured data into well-structured data. Now, as I mentioned, it's worth spending the time getting the data structure sorted out. So even if you are working with a legacy system or a legacy file, in a lot of cases, you can use power query to convert the legacy layout into the layout that you need that's easy to work with reports. So highly recommend that you investigate power query. It's been around for a long time now. Now it is fairly bullet proof. It is worth investing your time to learn about power query. And to be honest, there's not a lot of coding. You can do coding in power query, but so many things now in power query are built in. So it's just a question of selecting options and things like that. So highly recommend that you have a look at power query. And again, if you want to see how you actually do all of the changes, you can check out the video where I take you through step by step. Thanks for listening.

Outro:
Thanks for listening to the CPA Australia Podcast. For more information on today's episode, please visit the show notes at www.cpaaustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on Apple Podcasts, Spotify, or Stitcher.