Automating Data Cleaning with Power Query in Excel

1. Automating your data cleaning process with Power Query in Excel NathaliaGarayGabriel GonzalezHousing & Residential Life Files available at:https://tinyurl.com/uaccess-powerquery

2. 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

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

4. 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/

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

6. Where is Power Query? Excel2019 & Office 365 Excel 2016

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

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

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

10. 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)

11. 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

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

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

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

15. What is a Left Outer Join Merge? UAccess Table All from Housing Table, matching from UAccess Table Housing Table Merged Table

16. 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

17. 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

18. 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

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