Skip to main content
AmigoFish

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.

Watch on YouTube

Not rated yet

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…

Back to excelisfun · Report a problem