An Excel expert, Neale will present an Introduction to Power Query.
Power Query allows you to automatically perform data cleansing routines
on your data sources – no manual intervention required. Simply refresh and your
data is ready to use. You can use csv files; txt files; databases and existing
Excel tables as your data sources. Learn the basics, plus an advanced technique
to automate data cleansing routines on your data sources.
This session covers
·
fixing dates so that Excel can recognise them
·
formatting columns as text – retaining leading zeroes in CSV files
·
deleting unwanted rows and columns from your data
·
removing leading and trailing spaces
·
populating blank values with zeroes
·
populating blanks with entries from above
·
correcting trailing minus signs
·
unpivot a report – how to convert a report layout into a data table
layout
·
converting a MYOB report into a data table
For convenience Neale included pdf manual to Power Query in the attached
file, the printed version will be distributed at the session.
You are welcome to bring your laptop to the session and follow the
presentation using Power Query. For that you’ll need:
1.
For Excel 2010 and 2013 - to download and install a free Microsoft
add-in https://www.microsoft.com/en-au/download/details.aspx?id=39379 or for
Excel 2016 find ‘Get & Transform’ it in the Data ribbon tab;
2.
Save attached file on the desktop
As Neale is based in Perth, he will be presenting via video link.
Neale Blackwood has written over 170 Excel Yourself articles for the CPA
monthly magazine since 2002. He has also contributed 8 feature articles during
that time. He has been freely answering Excel related questions from CPAs from
all over the world for over 15 years.
His first book, Advanced Excel Reporting for Management Accountants, was
published by Wiley in 2014.
His Excel articles and companion videos appear on the ITBDigital
website. He has also recently appeared on the CPA Australia podcast.
Neale has been a CPA for over two decades and has accounting experience
in numerous industries. He has achieved the Microsoft recognized Excel Expert
Certification so this makes him doubly qualified to provide relevant Excel
assistance for accounting and finance staff.
He currently run his own business, A4 Accounting, supplying Excel
training, webinars and consulting services to organisations around Australia.
