Power Query #2: Import, Clean, Transform & Load Data in Excel
Download Zipped folder with files: https://excelisfun.net/files/PowerQuery02Files.zipFree 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
65. (51:44) Closing
#excelisfun #MikeGirvin #PowerQuery #Excel #PowerBI #PowerBIDesktop #Excel365 #LearnPowerQuery #freeclass #DataAnalysis #BusinessIntelligence #Microsoft #cleandata #transformdata
View Power Query Video pdf notes: https://excelisfun.net/files/PowerQuery-02.pdf Receive SMS online on sms24.me
TubeReader video aggregator is a website that collects and organizes online videos from the YouTube source. Video aggregation is done for different purposes, and TubeReader take different approaches to achieve their purpose.
Our try to collect videos of high quality or interest for visitors to view; the collection may be made by editors or may be based on community votes.
Another method is to base the collection on those videos most viewed, either at the aggregator site or at various popular video hosting sites.
TubeReader site exists to allow users to collect their own sets of videos, for personal use as well as for browsing and viewing by others; TubeReader can develop online communities around video sharing.
Our site allow users to create a personalized video playlist, for personal use as well as for browsing and viewing by others.
@YouTubeReaderBot allows you to subscribe to Youtube channels.
By using @YouTubeReaderBot Bot you agree with YouTube Terms of Service.
Use the @YouTubeReaderBot telegram bot to be the first to be notified when new videos are released on your favorite channels.
Look for new videos or channels and share them with your friends.
You can start using our bot from this video, subscribe now to Power Query #2: Import, Clean, Transform & Load Data in Excel
What is YouTube?
YouTube is a free video sharing website that makes it easy to watch online videos. You can even create and upload your own videos to share with others. Originally created in 2005, YouTube is now one of the most popular sites on the Web, with visitors watching around 6 billion hours of video every month.
