Excel 2016 Basics Free Course - Learn to Efficiently Use Excel at Work and to Get a Job. - Excel Basics 25: Power Query (Get & Transform) to Clean & Transform Data into Proper Data Set

4.0 (3)

24 learners

What you'll learn

This course includes

12.3 hours of video
Certificate of completion
Access on mobile and TV

Summary

Keywords

Highline College Busn 216 Mike Girvin Michael Girvin excelisfun Excel Basics Learn Excel Excel Excel 2016 Learn Excel Online Data Analysis Data Analysis in Excel

Full Transcript

Highline College Busn 216 Class: Computer Applications taught by Michael Girvin. Download Excel Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EB25GetAndTransformPowerQueryStart.xlsm Download Zipped Folder with Text Files: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EB25FileDownloads.zip Download Excel Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EB25GetAndTransformPowerQueryFinished.xlsm Download Full Written Notes: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/Office2016Class37-ExcelBasics25-PowerQueryGetAndTransform.pdf Class web page link: https://people.highline.edu/mgirvin/AllClasses/216v2016/Content/04Excel/Excel2016.htm Topics in this Video: Topics in this Video: 1. (00:01) Introduction to Power Query and the examples in this video 2. (02:50) Difference between Data Ribbon Tabs in Office 365 Excel 2016 and Non-Office 365 Excel 2016 3. (04:32) Example #1: Convert "NOT a Proper Data Set" into a Proper Data Set & Make PivotTable Report 4. (05:00) Compare Flash Fill and Power Query 5. (06:16) What is a Delimiter? 6. (07:02) What is a Query? In Power Query, Access and other programs? 7. (09:22) Import data from an Excel sheet into the Query Editor using From Table Range button 8. (10:18) Split By Delimiter 9. (11:30) Rename columns 10. (12:00) What is a Data Type? 11. (14:53) Close Query and Load to Sheet 12. (16:22) Queries Pane 13. (17:00) Make PivotTable 14. (18:04) Add New Data and Refresh 15. (20:12) Example #2: Import Multiple Text Files & Transform into Proper Data Set 16. (20:28) Download and unzip Text Files 17. (22:11) What is a Text File and how are they used in Data Analysis? 18. (24:02) Use From File, From Folder option to import multiple Text Files into Power Query and combine into a single Proper Data Set 19. (25:50) Use Query Editor to finish combine query. 20. (27:05) How to edit or delete steps in a query. 21. (27:05) How to edit the folder path if location of folder changes 22. (27:51) Close and Load straight to s Worksheet. 23. (28:30) Add new Text Files to Folder and update in Excel 24. (29:38) Summary