Power Query #2: Import, Clean, Transform & Load Data in Excel
From excelisfun · · 52 min

The player loads from YouTube only when you click. It uses YouTube's privacy-enhanced mode.
If the video does not play, the owner may have turned off embedding. Watch it on YouTube instead.
Description
Download Zipped folder with files: https://excelisfun.net/files/PowerQuery02Files.zip Free Power Query YouTube class from Mike excelisfun Girvin: https://www.youtube.com/playlist?list=PLrRPvpgDmw0lHaJfr4mjRcOMEYqKKvKBj Buy Mike Girvins Power Query M Code book: https://www.amazon.com/Transformative-Magic-Power-Query-Excel/dp/1615470832/
Topics: 1. (00:00) Introduction 2. (00:21) Download files to good location for Power Query & unzip folder 3. (02:00) Source data files for import 4. (02:23) Turn file extension on in Control Panel 5. (03:18) Open Excel Start file & look at video project goals 6. (03:58) What is cleaning data? 7. (04:21) What is transforming data? 8. (04:53) PDF notes for video #2: free book! 9. (05:12) What is Power Query in Excel? 10. (05:35) Structure of data in a CSV file 11. (06:01) What is a delimiter? 12. (06:22) Import Csv File 13. (07:16) Introduction to Power Query Editor 14. (07:44) Naming a query 15. (08:03) Applied Steps 16. (08:07) Changing Data Load Settings in Options 17. (09:05) Introduction to M Code in Applied Steps & Formula Bar 18. (09:52) M Code is case sensitive 19. (10:31) On Premises File & Folder Paths to link Source Data and Destination Data 20. (11:50) Data Types to create consistent data 21. (12:24) let expression and the Advanced Editor 22. (13:47) Naming convention for Identifiers: NO SPACES!!!!!! 23. (15:08) Close & Load To, Only Create Connection 24. (15:58) Queries & Connections task pane 25. (16:07) Structure of data in an Excel file 26. (17:17) Import data from an Excel file 27. (19:59) Structure of data in an Access database file 28. (20:17) Import data from an Access file 29. (21:05) Compare how data arrives in the Power Query Editor from Csv, Excel, and Access file sources (data type difference) 30. (22:18) Edit an existing query by opening the Power Query Editor 31. (22:48) Clean Data: Split By Delimiter to create Product ID & Sales Channel columns 32. (23:40) Edit M Code in Formula Bar and delete query steps to learn how editing query steps can affect subsequent steps 33. (26:52) Clean data: convert ISO Date to Real Date 34. (27:38) Using Local feature to convert International Dates to Local Dates 35. (28:42) List of all 20 Data Types 36. (28:48) List of all 15 M Code Values, and define Expression 37. (30:18) Defining Lookup or Merge in Power Query M Code 38. (30:48) Transform data: Merge / Join feature to lookup product price and name 39. (32:48) Transform data: Merge / Join feature to lookup location discount 40. (33:16) Error in matching lookup value 41. (33:43) Clean data: Replace Values to create correct lookup values 42. (35:06) Transform data: Add Column Multiply feature 43. (36:04) Table.AddColumn function 44. (36:33) each keyword 45. (36:50) Field Access Operator 46. (37:03) Transform data: Use Custom Column feature to calculate Net Sales 47. (38:44) Rounding in Power Query, not with ROUND, but with: Number.Round 48. (39:20) Function names are M Code Value specific, such as Number, Text, Table, List 49. (39:46) Typing convention for M Code functions 50. (40:24) Custom Column one-step method to calculate Net Sales 51. (41:30) Edit Custom Column in dialog box 52. (41:52) Transform data: Remove Other Columns 53. (42:23) Finished let expression 54. (42:39) Loading a query that has already been loaded 55. (42:41) How to edit load location to load data to an Excel Table in the worksheet 56. (43:10) Create PivotTable and Chart from Query Output in an Excel Table 57. (43:27) What is PivotTable Cache? And why you must refresh twice. 58. (44:31) Creating & Formatting PivotTable & Chart 59. (46:27) Source Data Changes? Refreshing twice when Query is in an Excel Table and PivotTable is created from Excel Table 60. (47:32) DataSource.NotFound Error and how to fix it. 61. (48:36) Data Source Settings 62. (49:26) Refreshing only once when you load directly to PivotTable Report (PivotTable Cache) 63. (50:54) Summary 64. (51:33) Mike excelisfun Girvin’s Power Query M Code Book…