Month: July 2018

MSPTDA 08: Power Query Group By feature & Table.Group Function (5 Examples)

Posted on by Annie D

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.

View on YouTube

Excel Magic Trick 1511: Array Formula: Get Low Bid From Supplier When Delivery Less Than 20 Weeks

Posted on by Annie D

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.

View on YouTube

MSPTDA 07: Power Query: 6 Types of Joins, 6 Types of Merges: 9 Examples

Posted on by Annie D

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.

View on YouTube

Excel Magic Trick 1509: Conditional Format Array Formula to Highlight Row With 2 Lookup Values

Posted on by Annie D

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.

View on YouTube

MSPTDA 06: Power Query: Merge, Append, & UnPivot – 3 Important Transformations

Posted on by Annie D

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.

View on YouTube

MSPTDA 05: Power Query: Excel.CurrentWorkbook Function to Append All Excel Tables in Excel Workbook

Posted on by Annie D

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.

View on YouTube

MSPTDA 05: Power Query: Excel.CurrentWorkbook Function to Append All Excel Tables in Excel Workbook

Posted on by Annie D

Welcome to another awesome power query tutorial!

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.

View on YouTube

MSPTDA 05: Power Query: Excel.CurrentWorkbook Function to Append All Excel Tables in Excel Workbook

Posted on by Annie D

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.

View on YouTube

MSPTDA 05: Power Query: Excel.CurrentWorkbook Function to Append All Excel Tables in Excel Workbook

Posted on by Annie D

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.

View on YouTube

MSPTDA 05: Power Query: Excel.CurrentWorkbook Function to Append All Excel Tables in Excel Workbook

Posted on by Annie D

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.

View on YouTube

Excel Magic Trick 1508: Lookup In Current Table? Self Join? VLOOKUP or Power Query?

Posted on by Annie D

Download Files:
https://ift.tt/2KFamW4
https://ift.tt/2J0l187
https://ift.tt/2KEthR5

Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs

In this video see how to lookup a referral ID in an employee table to return the employee name, but the catch is that the Lookup Return Column is in the Lookup Table. See how to do it in Excel with VLOOKUP and in Power Query with a Merge Left Outer Join that joins the employee table to itself.

Examples in this video:
1. (00:06) Introduction & Description of problem
2. (00:58) Excel VLOOKUP Example
3. (04:18) Power Query Example
4. (08:03) Summary

View on YouTube

MSPTDA 04: Power Query: Import Multiple Excel Files & Combine (Append) into Proper Data Set

Posted on by Annie D

Download START Files:
https://ift.tt/2KJtePH
https://ift.tt/2tSfMTh
https://ift.tt/2u4Fl2J
Download FINISHED File: https://ift.tt/2NjsouZ
Download pdf Notes about Power Query to import Excel data: https://ift.tt/2KNHF5E
Assigned Homework: coming soon…

In this Video learn how to import data from multiple Excel Workbook Files and append into a single Proper data Set.
Topics:
1. (00:12) Introduction
2. (02:18) Look at Data Import Files and the different objects that are in an Excel File
3. (06:56) Import Excel Files From Folder
4. (08:11) Look at Excel File in Power Query Editor
5. (08:26) Transform extensions to all lowercase
6. (08:34) Filter to include only Excel Files in import process
7. (09:10) Extract Excel File Name to create New Column for City. Split By Delimiter.
8. (10:01) Power Query Options: Don’t Change Data Type
9. (11:10) Rename Column and Remove unwanted columns
10. (11:34) Add Custom Column with Excel.Workbook Function (M Code Function). Explanation of what functions extracts from the Excel Files.
11. (15:14) Filter Out Excel Objects that do not meet Criteria = Sheet
12. (15:37) Filter out names that Do Not Begin With Sheet. Extract Worksheet Name to create New Column for SalesRep.
13. (16:08) Final Append to get all Excel Worksheet that contain Proper Data Sets with a proper SalesRep Name.
14. (17:41) Apply correct Data Types
15. (18:50) Load to Excel Sheet
16. (19:41) Change Default PivotTable Layout & Options
17. (21:19) Build PivotTable Report
18. (23:40) Definition of a PivotTable
19. (26:12) Add New Excel Workbook Files to the Folder & Refresh the Query and PivotTable
20. (29:35) Edit Query when Folder Path Changes
21. (30:57) Summary

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube