Blogs

The best way to get data into Excel

By Neale Blackwood Blackwood, CPA posted 17-09-2020 11:18 AM

  
Power Query is a relatively new feature in Excel, but not that new. Unfortunately so many people seem to be totally unaware of its existence.

In terms of importing data into Excel it is easily the most important feature in the last 20 years.

Power Query allows you import data from virtually any source and then transform the data and fix issues and add and remove columns to get the data just right.

Then once you have created the query to get the latest data in the transformed structure it is just a refresh. It remembers all the steps you took to fix the data. Its a bit like a recorded macro but even better.

Getting your data right is the biggest hurdle in Excel. When the data is structured correctly using formulas and Pivot Table becomes a lot easier.

Many times systems export data into Excel in poorly structured formats, Power Query can fix pretty much any badly structured data.

I believe ALL accountants should know about Power Query. It has so many applications and can simplify so many processes and save so much time. It is a must have productivity skill. It can automate the data importation process and make reporting that much easier and quicker.

Power Query is also part of Power BI - Microsoft's dashboard and report application. Any Power Query skills you learn in Excel are directly applicable to Power BI. (BI stands for Business Intelligence).

Versions
There are free Power Query add-ins for Excel 2010 and 2013. It is built-in to Excel 2016 and later versions, in the Data ribbon in the Get & Transform section..

I have written quite a few articles on Power Query, Each has a companion video.You can check out the articles and videos at the links below.  I also recorded a podcast on it - link below.

If you have any questions about Power Query please leave them in the comments.

My Articles

https://www.intheblack.com/articles/2016/06/01/an-easier-way-to-use-csv-files-in-excel

https://www.intheblack.com/articles/2017/08/01/master-excel-power-query

https://www.intheblack.com/articles/2018/07/01/how-to-harness-power-query-excel

https://www.intheblack.com/articles/2019/12/01/understanding-excels-power-query-date-tables

https://www.intheblack.com/articles/2020/03/01/use-power-query-to-solve-common-excel-budget-issues

Liam Bastick Interview Article
https://www.intheblack.com/articles/2019/10/08/saving-time-with-excel-power-query

Podcast
https://www.cpaaustralia.com.au/podcast/excel-for-accountants-power-query

#Excel
#Data​
#PowerQuery​
#Highlights
6 comments
369 views

Related Content

Permalink

Comments

Neale Blackwood Blackwood, CPA
27-09-2020 09:54 PM

HI @Anthea White White, CPA
Yes Power Query offers lots of opportunities to automate the data importation process and save a lot of time and effort. Glad you found the content worthwhile.
Regards
Neale  ​

Anthea White White, CPA
27-09-2020 01:00 PM

Hi everyone,

I've been busy learning all about power query, thanks in no small way to Neale's articles in ITB, and through Excel Yourself, & A4Accounting, and incorporating it into my work more & more.  I highly recommend familiarising yourself with it.  So far it's saved me quite a lot of time data cleansing!

I also recommend Neale's tutes, training & webinars on a huge range of other Excel related topics.

Neale Blackwood Blackwood, CPA
25-09-2020 07:55 PM

Hi @Anson Hu, CPA 
Glad you like the articles. This blog will give me a chance to bring some of the content together and also share new content.​
There is a bit of lead time on the articles, so I can be a bit more agile on this blog.

Regards
Neale

Anson Hu, CPA
25-09-2020 07:16 PM

Hi Neale

Good to see you have blog here.

I am big fan of your articles in ITB magazine.

Neale Blackwood Blackwood, CPA
20-09-2020 01:17 PM

Hi Tim

Yes, Power Bi is the way of the future. Excel will still have it's place but more and more reporting will be done in Power BI. I will be sharing some of my Power BI articles soon.

Regards
Neale

Tim Lee, CPA
20-09-2020 12:24 PM

An excellent article - I'm a massive fan of Power BI.  It is highly interactive and visual, allowing for both high level analytics as well as drilling down into the granular.

I have used this tool with much success when conducting a demo for clients.  They are usually blown away with it ability to convey complex data in an easy to understand graphical format.