Download Excel File: https://ift.tt/2BNJeQM
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video see how to count values that are greater than a hurdle when the values in in noncontiguous cells (cells not next to each other). See an Array Formula that uses SUMPRODUCT and CHOOSE and a Logical Formula.
Download Excel START Files: https://ift.tt/2L7OwpO
Download Excel FINISHED Files: https://ift.tt/2MsL7Hs
Download pdf Notes about Power Query: https://ift.tt/2wkNW2K
Assigned Homework:
Download Excel File with Homework: https://ift.tt/2MmtxFf
Example of Finished Homework: https://ift.tt/2L7OxtS
In this Video learn the basics of M Code, the computer language behind queries in Power Query.
Topics:
1. (00:15) Introduction
2. (03:46) Edit M Code: Applied Steps
3. (03:46) Edit M Code: Formula Bar
4. (03:46) Edit M Code: Advanced Editor
5. (09:50) Expressions
6. (09:50) let expressions
7. (17:34) Comments in M Code
8. (21:11) Values: Primitive, List, Record, Table, Function
9. (30:45) Lookup or Projection and Selection. Learn about Row Index Lookup and Key Match lookup
10. (42:50) Primary Keys
11. (50:20) Custom Functions
12. (57:44) Parmenter Queries
13. (01;02:27) Underscore Character _
14. (01:06:17) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Download File: https://ift.tt/2AYwOFj
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video see how to count how many employees have worked for the company between 1 and 6 years, based on a hire date. See 8 examples of different formulas and Conditional Formatting.
Topics:
1. (00:06) Introduction
2. (01:13) TODAY Function
3. (01:44) EDATE Function for Lower Limit for counting between lower date and upper date. EDATE for upper limit formula too.
4. (03:08) COUNTIFS to count between lower and upper dates. Learn about how the comparative operator in COUNTISF requires quotes. Formula Counts Between a Lower & Upper Limit.
5. (04:41) AND Function Helper Colum for logical TRUE / FALSE formula. Learn about how the comparative operators in Logical Formulas do NOT require quotes.
6. (06:35) COUNTIFS function with TRUE criteria. Count Number of TRUE values.
7. (06:57) SUMPRODUCT Function to add the number of TRUE values. Add Number of TRUE values.
8. (08:47) Conditional Formatting Formula to highlight the employee records (highlight row) where the employee has worked for company between one to six years.
9. (11:40) One Complete Mashed Up Formula that does not require intermediate cells with formulas. Learn a lot of how you can copy and paste formula elements from intermediate cells into one final formula – huge mega formula.
10. (13:46) Summary
Download Excel START Files: https://ift.tt/2MqR0km
Download Excel FINISHED Files: https://ift.tt/2MfNGfr
Download pdf Notes about Power Query: https://ift.tt/2veIr4P
Assigned Homework:
Download Excel File with Homework: https://ift.tt/2M4R2Sl
Example of Finished Homework: https://ift.tt/2OjHnob
In this Video learn how to use Power Query’s Group By feature to Group By and create a unique list with aggregate calculations or create a Group By Report based on Consecutive Occurrences of items in a given column with aggregate calculations.
Topics:
1. (00:15) Introduction
2. (00:37) What is Group By Report based on Consecutive Occurrences?
3. (01:27) Group By feature to Group By and create a unique list with aggregate calculations
4. (03:15) Learn about how Gear Icon can Disappear when you alter the M Code, which means the dialog box disappears.
5. (05:12) Learn about the difference between Duplicating a Query and Referencing a Query.
6. (05:12) Group By Report based on Consecutive Occurrences of items in a given column with aggregate calculations. Use the forth argument and GroupKind.Local
7. (07:27) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Download Files: https://ift.tt/2LQIbEp
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video see how to color a row with conditional formatting using the COUNTIFS function, an Expandable Range and a Comparative Operator to convert formula to a Logical Formula. See how to use the Conditional Formatting Dialog Box with a Logical Formula. Also see how to use Conditional Formatting on an Excel Table, so new rows are formatted when new records are added..
In this video learn about the new Excel Hash Iron Chef Tournament that Six Excel YouTubers will compete in. the Excel YouTubers Excel is Fun • Leila Gharani • MrExcel • Excel on Fire • Mynda Treacy • Jon Acampora will compete in a Excel Cooking contest to create Awesome Excel Solutions from a list of Excel Features!
Download Excel START Files:
https://ift.tt/2veJ0vt
Download Excel FINISHED Files:
https://ift.tt/2LvyjQd
Download pdf Notes about Power Query: https://ift.tt/2veIr4P
Assigned Homework:
Assigned Homework:
Download Excel File with Homework: https://ift.tt/2OmVQAN
Example of Finished Homework: https://ift.tt/2AmjM4b
In this Video learn all about Power Query’s Group By feature. See Four Examples of how to use Group By to Calculate Total Sales, Standard Deviation, Join Names and Rank Sales.
Topics:
1. (00:15) Introduction
2. (01:30) What Does Power Query Group By feature do?
3. (02:58) Examples of PivotTable, SUMIFS, Data Model PivotTable and SQL Code to see that Power Query Group By is VERY Similar
4. (05:55) Example of Simple Power Query Group By Command for Total Sales
5. (08:50) First Look at Table.Group Power Query Function
6. (10:17) Example of using Group by to get Tables of Grouped Records, or Matching Records
7. (11:55) Standard Deviation Custom Column Based on Grouped Records Table
8. (14:41) Joined Text Items from Unique List formula in Custom Column Based on Grouped Records Table
9. (16:23) Detail look at Table.Group Function. Edit and create arguments in Third Argument to List Multiple New Columns with Formulas
10. (21:32) Power Query Group By to Help Rank Sales for Each Product
11. (26:24) Group By More Than One Column
12. (27:11) Fast Trick for Group By: Placeholder Function
13. (28:51) Add new data and see that everything updates.
14. (29:38) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Download File:
https://ift.tt/2AcigSi
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video see how to create an Array Formula to get the low bid from Supplier when Deliver is Less Than 20 Weeks, and when the Data Set is not in Proper Data Set Form, which makes the formula a bit tricky. See the MIN and IF Functions that use Array Operations.
Download Excel START Files:
https://ift.tt/2LwCF91
Download Excel FINISHED Files:
https://ift.tt/2JLJr5F
Download pdf Notes about Power Query: https://ift.tt/2LrkvFv
Assigned Homework:
Download Excel File with Homework:
In this Video learn about Power Query’s 6 types of Joins, 6 Types of Merges.
Topics:
1. (00:15) Introduction.
2. (00:24) Full Description of What A Join is and what a merge is. What is A Join?
3. (06:06) Inner Join
4. (13:05) Create Folders to organize Queries
5. (13:35) Full Outer Join
6. (17:55) Left Anti Join
7. (20:30) Right Anti Join
8. (23:27) Left Outer Join for Lookup
9. (26:16) Left Outer Join for Two Value Lookup
10. (28:28) Left Outer Join to pull matching Multiple Records from the Right Table (One Lookup Value, Return Multiple Items).
11. (29:41) See how to use Expand Column to Aggregate Values.
12. (31:53) Right Join Outer
13. (34:37) Self-Join
14. (36:56) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Download Files:
https://ift.tt/2zTfxwK
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video see how to color a row with conditional formatting using an Array Formula & the MATCH Function that will lookup Two Lookup Values in a corresponding Table.
Download Excel START Files:
https://ift.tt/2mifhh5
https://ift.tt/2zEx2kt
https://ift.tt/2mifhO7
Download Excel FINISHED Files:
https://ift.tt/2uxNo8v
Download pdf Notes about Power Query: https://ift.tt/2mifil9
Assigned Homework:
Download Excel File with Homework:
Assigned Homework:
Download Excel File with Homework: https://ift.tt/2zEx2Rv
Download Files to import for Merge Homework:
https://ift.tt/2mhtsmP
https://ift.tt/2zEx3ox
Example of Finished Homework:
https://ift.tt/2mhttqT
In this Video learn about three important Power Query Transformations: Merge, Append and UnPivot.
Topics:
1. (00:15) Introduction.
2. (02:00) Example1: Merge 2 Tables to Replace VLOOKUP or Relationship
3. (08:11) Example 2: Transform 3 Non-Uniform Sales Tables Then Append into Single Proper Data Set
4. (16:31) Example 3: UnPivot to Convert Cross Tabulated Data Tables into Proper Data Set
5. (22:29) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Download Excel START Files:
https://ift.tt/2L6uYCT
https://ift.tt/2L6jfYj
Download Excel FINISHED Files:
https://ift.tt/2La3uMM
https://ift.tt/2L9mZsh
Download pdf Notes about Power Query: https://ift.tt/2LbMtSy
Assigned Homework:
Download Excel File with Homework: https://ift.tt/2KZofOx
Example of Finished Homework: https://ift.tt/2Le3lYU
In this Video learn all about Excel.CurrentWorkbook Power Query Function to import all the Excel Tables in the Current Workbook, including all the potential pitfalls of using this function and how to get around these pitfalls.
Topics:
1. (00:15) Introduction to Video and to Excel.CurrentWorkbook Function, including correct definition of what Excel.CurrentWorkbook Function imports when this function is invoked.
2. (02:10) Example1: Append all Excel Tables in Current Workbook To Worksheet. We will see the Recursion Problem and solve it by filtering out the Query/Table Name.
3. (03:15) Create a Blank Query.
4. (04:10) Use Excel.CurrentWorkbook() Function as Source for Query.
5. (04:50) Use Replace feature to extract the SalesRep name from the Excel Table Name.
6. (05:38) Expand column with Excel Tables to Append all Tables into one Table.
7. (05:56) Add correct Data Types for each column
8. (06:22) Introduction to Recursion Problem, where Query will refer to itself and will double the loaded records every time a Refresh is done. And look at details of Loading Data to an Excel Worksheet after using Excel.CurrentWorkbook() Function.
9. (10:07) Solve the Recursion Problem by filtering out the Query/Table Name.
10. (11:03) Add new Excel Table to Workbook and refresh to see that new table is incorporated into Final Data Set.
11. (11:40) Look at M Code for Example #1
12. (12:20) Example2: Append all Excel Tables in Current Workbook To PivotTable Cache & make PivotTable Report. This solves the Recursion Problem because there is not a Query Load table in the Excel Worksheet as an Excel Table.
13. (12:41) Remove Excel Table that is result of Power Query Load to Worksheet by Clearing All. This process will change the Load location to Connection Only.
14. (13:38) Edit Query to Remove unneeded step and to Rename incorrectly named column.
15. (14:28) Look at M Code for Example #2
16. (15:05) Example3: Append all Excel Tables in Current Workbook that has Defined Names.
17. (15:12) Look at different objects in Excel workbook, including Excel Tables and Defined Names.
18. (17:07) Keyboard for Blank Query
19. (17:40) Use Excel.CurrentWorkbook() Function as Source for Query. Then see that this imports Excel Tables and Other Objects.
20. (17:51) Define Table Object: Set of Records for a Set of Columns/Fields.
21. (18:50) Take note that Defined Names are Imported as Tables with generic Columns Names.
22. (19:50) Learn about Table.ColumnNames Power Query Function.
23. (19:59) Filter out Filtered Database Error.
24. (20:29) Create Custom Column and use Table.ColumnNames Power Query Function to Extract Column Names from each Table in each Row.
25. (20:55) Learn about Lookup Operator or Field Access Operator to access the content for each row in a specified column.
26. (21:08) Define List Object: Ordered Sequence of Values.
27. (21:35) Learn about the Positional Index Operator that allows us to access an item in a list. Curly Brackets are the Positional Index Operator; { and } .
28. (22:27) Learn that Power Query uses Base Zero for finding Relative Positions in a List. Zero represents the first position.
29. (23:06) Filter out rows that contain “Column1”.
30. (23:18) Remove Custom Column.
31. (23:23) Rename Column
32. (23:27) Use Replace feature to extract the SalesRep name from the Excel Table Name
33. (23:41) Filter Out Query Name / Table Load Name when loading to an Excel Worksheet.
34. (24:07) Expand Columns and Change Data Types
35. (24:24) Closes and Load To Worksheet.
36. (24:46) Add new Excel Table and Refresh.
37. (25:07) Look at M Code for Example #3
38. (26:25) Talk about the non-standard Data Setup we had to deal with.
39. (26:51) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.