How to import Excel files using Power Query

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

Neale Blackwood CPA:
In the September 21 article, I covered how you can import multiple Excel files into Excel and put them in a single table. The idea is that you have a folder, and in that folder you have a number of Excel files. Some of them you don't want to import. So we can filter those ones out. I've used a naming convention. And using that naming convention, you can specify the files, the Excel files that you want to import.

Neale Blackwood CPA:
Now, the structure is important here. So the theory is that all of these files that we're importing are exactly the same layout. Now, they've got different values, but the structure is an output from, let's say, a reporting system. And so it's consistent. And maybe the example here uses different files for the states. So each state has a separate file that we need to import.

Neale Blackwood CPA:
Now, the video of the article which is on the INTHEBLACK magazine website has the folder that you can download and then you can follow the video through. So if you want to learn how to do it, then I highly recommend the video. It'll take you through the 24 steps that are listed in the article, and explain quite a number of different things as you go through. In this podcast, I thought I'd just cover some of the main items that I discussed and just some ideas on when you're importing Excel files.

Neale Blackwood CPA:
Now, the beauty of this is, once it's done it's scalable. So if you add another, let's say, another state report to the folder, then next time you refresh the data, it comes through automatically. So you don't have to do anything different. As long as the report is the same layout, it's all good. It'll just get updated automatically.

Neale Blackwood CPA:
Now, I've used a naming convention and also I've used the underscore character to separate the different sections in the name. So the naming convention that I used was report underscore, and then August or AUG2021_NewSouthWales. So the report underscore is at the start of the folder name. And then actually the rest of the name, I can use to create some of the columns that we want to capture.

Neale Blackwood CPA:
By using the underscore character, when we import the data into Excel, one of the columns is actually the file name. And so we can chop that file name up using the underscore character. And also we use that to filter out any other reports that we don't want because there's two other files in the folder that have the word "report" on the end and they also don't have any underscores in them. So we can use a filter to remove those files, and then we can just concentrate on the ones that we want to bring in.

Neale Blackwood CPA:
And then the name column that comes in, we can chop that up. It's called splitting, and we can split based on what's called a delimiter, which is a character that identifies where you want to split the column. In our case, it will split the single column which has the file name into report. And then the next column will be August 2021. And then the next column would be New South Wales, or basically the state would be in the third column.

Neale Blackwood CPA:
And that's actually two components of what I wanted to extract. So Power Query, automatically identifies the August 2021 as a date, and it converts it into a date data type. Just splitting up that name gives me two columns that I wanted to capture. And again, using a naming convention just makes some of the operations in a Power Query just so much easier. So I highly recommend that you use a naming convention and stick to it. It's got to be consistent for it to work.

Neale Blackwood CPA:
When you are filtering in Power Query, you are reducing the number of rows that are imported, which is always a good thing. So reducing the number of rows. It's also a good idea to try and minimise the number of columns you bring in. So only bring in the columns that you need. Now, when you delete columns, and I'll take you through some things you can do when deleting columns, you can, if you change your mind, go back to the query and amend it and maybe change the column. So you can do a little bit of trial and error. And trial and error is pretty common when you are building power queries because something you think might work might not work so well when you actually find the output and check it against the input. And I'll mention some things about that a little later. In general, if you can reduce columns, that saves more space than reducing rows because obviously columns have every row in there. And so if you can reduce the number of columns, that does reduce the size of the table that you are importing.

Neale Blackwood CPA:
Now, in the example, I used the null keyword a few times. Null is power queries word for empty or blank. So null, when you refer to null, it's always a lower case. Null, we use it in a formula. So there's one formula in the example that helps us identify and extract out the category. The null keyword is used there. Also, null is used a couple of times to filter out some of the rows that we don't need.

Neale Blackwood CPA:
Now, one of the advantages with Power Query is, when you filter a column, the null option is always at the top, which is really, really useful because in Excel, the Excel term for null is blanks. And when you filter a column in Excel, the blanks is always the last entry. So you've got to scroll down all of the entries to get to blanks. So Power Query is much better there because it's very quick to actually filter out null entries.

Neale Blackwood CPA:
Null's also useful. And when we did the... In the video, when we capture the category column, when we are capturing the category column, we bring in the category across, or we put in the null keyword. And the beauty of the null keyword is, you can use the fill down operation. Fill down is when you have an entry on one row and then the entries below it are blank or they're null. And then you have another entry and again, another list of null. And what you want to do is you want to fill down. You want to populate all of the rows below the entry with the entry above. And that's three mouse clicks when you are using Power Query. So you right click the column, you choose fill, and then you choose down. And that populates all of the column with the value above being copied to the blank rows below. And null allows you to do that because null is the entry that will overwrite.

Neale Blackwood CPA:
When it comes to deleting columns that I mentioned earlier, there's a few ways to do it. So it's actually called removing columns in terms of the terminology. So you can do them one at a time and you can also use the keyboard. You can press delete on your keyboard to remove a column. Just select the column and hit delete. You can select multiple columns and hit delete. And another way to do it is to select the columns you want to keep, and then right click one of their headers on the columns that you are keeping and you can choose remove other columns. Okay? That way's a little bit more flexible in that you're identify find the columns that you want to keep. And then pretty much all of the other columns no matter how many will get removed. So that's a really useful way to do it. A really flexible way to remove the other columns that you don't need.

Neale Blackwood CPA:
Now, when you bring in multiple files, or when you're combining multiple files, you're typically have heading rows or heading entries in value columns. And now to get rid of them, one of the tricks is to change the data type for the column. So the data type is a little icon that's on the left-hand side of the header, and change that to a number. So whether it's a whole number or a decimal. And what that does is, any headings in that column, so any text will get converted into an error because they can't be converted into a number. And that then allows you to filter out those headings. So once you've changed the data type to a number, the heading entries in there will change to the error word, and then you can just right click and you can remove errors. So again, that's another little trick to get text out of a column that should have just values in there.

Neale Blackwood CPA:
Now, one thing with filters, it does take a little bit of trial and error. So don't expect it to get it correct the first time. Always check the output to make sure that you're not missing something that you should be bringing in. So compare it with the input. And again, you might need a little bit of trial and error to get the right type of filter.

Neale Blackwood CPA:
Just a note, I did mention it in the video, when there's a step in a Power Query and on the right-hand side of the step and all the steps are under the applied steps heading. If there's a little cog icon on the right-hand side, it means you can edit that particular a step. Now, whether it was a filter or something else. If the cog isn't there, then you might need to delete it and then redo it. Now, you have to be careful when you're doing that type of thing if the steps are in the middle of a number of steps because it will warn you. If you're trying and delete it, it will say that things could break. And a lot of cases they will, but you can then redo the step and hopefully fix it. So sometimes, especially when you're starting out in Power Query, you might just need to click at the bottom and delete all of the steps going up until you get to the one you want to change and then start again, which isn't too bad because it gives you a little bit more practise.

Neale Blackwood CPA:
Also, something that I mentioned, when I'm around working with Power Query, there is no undo. There's no undo button, but you can undo things by, for example, deleting the steps. There's the little red cross on the left hand side of the step name, and you can delete the steps. There is no undo or redo button in Power Query. So that's something you got to ahead around. But once you start using Power Query, you realise that doing things is quite quick. So even though it might take you a little bit of time, it's not a big deal that you can't undo things. I believe there is a request into Microsoft to get the undo and redo added to Power Query, but we'll just have to wait and see.

Neale Blackwood CPA:
Now, Power Query is updated pretty consistently. Power Query is also part of Power BI. So Power BI is Microsoft's dashboarding and reporting package. It works pretty seamlessly with Excel. And anything you learn in Power Query, it can be directly applied to Power BI as well. The Power Query in Excel and Power BI are pretty similar. The Power BI version is usually more up to date because it gets updated pretty much monthly whereas in Excel you tend to be, I think it's roughly every six months. It does depend. There is a setting you can change to get updated more often. It just depends. I think it's called the Update Channel or something like that. And you can get more updates in like monthly or something, but it just depends which one you've chosen. But Power Query is updated regularly. You don't have to wait like the old days whereas like three years between Excel updates. So especially if you've got the subscription version of Excel, you get fairly regular updates.

Neale Blackwood CPA:
Okay. Power Query, extremely powerful. I highly recommend all accountants know about Power Query and know what it can do. Most accountants, to be honest, should be able to use Power Query. Unfortunately, it's slipped under the right data a little bit. It's been around for many years now. There are lots of articles on the INTHEBLACK website on Power Query. So you can go do a search and check those out. Power Query, a really useful skill set to have, especially as more and more data is important, and you can combine the data from multiple systems into the one file and then create your dashboards reports.

Neale Blackwood CPA:
Power BI is probably the future of dashboards. And you might... However, there is a little bit of a learning curve with Power BI, but you might be able to prototype some things in Excel using Power Query and also Power Pivot, which is also part of the subscription version of Excel. Okay. I hope that's been useful. And 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.edu/podcast. Never miss an episode by subscribing to our podcast on Apple Podcasts, Spotify, or Stitcher.