Automating your data cleaning process with Power Query

Automating your data cleaning process with Power Query in Excel Nathalia Garay Gabriel Gonzalez Housing & Residential Life Files available at: https: //tinyurl. com/uaccess-powerquery

Agenda • • What is Power Query in Excel? Power Query vs Analytics vs “Regular Excel” Overview of Power Query Demo 1: Appending (joining) files Demo 2: Merging (combining) files When to use Power Query Advantages of using Power Query Q&A Files available at: https: //tinyurl. com/uaccess-powerquery

Power Query Files available at: https: //tinyurl. com/uaccess-powerquery

What is Power Query? Power Query is an ETL tool, which stands for: 1. Extract data from almost any source 3. Load (to Excel) 2. Transform with Power Query Source: https: //www. excelcampus. com/powerquery/power-query-overview/

The best of all worlds UAccess Analysis Data is always up to date Power Query Once query is set up, new data is transformed with a click of a button “Regular Excel” Robust, flexible and easy to use

Where is Power Query? Excel 2019 & Office 365 Excel 2016

Power Query UI 1. Ribbon Section 2. Data Preview 3. Query Settings

Demo 1 Appending (joining) multiple files Files available at: https: //tinyurl. com/uaccess-powerquery

Demo Overview File #1 File #2 File #3 Source: https: //www. excelcampus. com/powerquery/power-query-overview/

Keep in mind • You need a folder exclusively for the files you are appending • Appending works best if source files have the same columns, however this is not required • As you add new files to the folder, remember to refresh the query and the pivot table (optional)

Other appending examples • Monthly/Weekly reconciliation of P-Cards, accounts, grants, inventory, etc. • Loading information from PDF files to Excel • Running the same report of rapid changing data for events

Demo 2 Merge two tables, an alternative to VLOOKUP Files available at: https: //tinyurl. com/uaccess-powerquery

Demo Overview Join (merge) matching fields Add “lookup” field to table. No formulas required Source: https: //www. excelcampus. com/powerquery/power-query-overview/

What kind of Join should I use? Left Outer Join UAccess Housing Right Outer Join UAccess Housing Inner Join UAccess Housing Full outer Join UAccess Housing Full Anti Join UAccess Housing Left Anti Join UAccess Housing Right Anti Join UAccess Housing

What is a Left Outer Join Merge? Housing Table STD ID STD Name Is Joined 001 T. Brady ✔ 002 A. Rodgers ✔ 024 T. Tebow ✔ 030 P. Manning ✔ Merged Table All from Housing UAccess Table, matching STD ID GPA Is Joined from UAccess Table 001 2. 00 ✔ STD ID STD Name 001 T. Brady 002 A. Rodgers 024 T. Tebow 030 P. Manning 002 020 024 050 GPA 2. 00 1. 50 4. 00 null 1. 50 4. 00 3. 25 2. 75 ✔ ✔

Other merging examples • Merging data from two or more subject areas in UAccess • List of student workers (Employee) and their GPA (Student) • List of students (Student) enrolled in a class that have outstanding charges (Financial) • Think Tank usage of students living in the dorms

When should I use Power Query? • When you frequently make changes to the same dataset – Demo 1 • When combining two or more files – Demo 2 • When working with large datasets that you normally analyze with pivot tables or visuals • When data is pulled from remote sources

Advantages of using Power Query • Intuitive UI that allows easy manipulation of data as opposed to lengthy Excel formulas • Keeps track of all changes; changes are easy to undo • Can function like a Macro, but does not require programming knowledge • Has powerful formulas for advance data manipulation • Easy way to unpivot data

Questions & Thank You! ngaray@email. arizona. edu ggracia@email. arizona. edu Files available at: https: //tinyurl. com/uaccess-powerquery