In this recording of a live session ran in late January 2023 you will learn how to automate data importation in Excel with Power Query. If you use data at all then Power Query is an essential skill to possess.
Use the buttons below the video to download the materials.
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 – converting a report layout into a table layout
- converting a MYOB report into a data table
- capture header information in a column
As always, I shared other tips and tricks along the way.