Category: Power Query

Power BI M Code for Moving Annual Total (MAT): Custom Function Power Query Custom Column – MSPTDA 10

Posted on by Annie D

Download Power BI Desktop START File: https://ift.tt/2BXmQEV
Download Power BI Desktop FINISHED File: https://ift.tt/2MZj7e5
Download pdf Notes about Power Query: https://ift.tt/2BXmSg1
Download Excel File with parallel Excel Example: https://ift.tt/2ojpNpi
Assigned Homework:
Download pdf file with homework description: https://ift.tt/2PfRSts
Example of Finished Homework in Power BI Desktop: https://ift.tt/2ojpP0o

In this Video learn Power Query M Code and Custom Functions to calculate Moving Annual Toatls.
Topics:
1. (00:15) Introduction
2. (01:10) Comment from YouTube that inspired the video. Verbal Description of the Data Model Transformation we want to make, including the Moving Annual Total Calculation.
3. (02:07) Thanks to Bill Szysz for Custom Function.
4. (02:18) Excel Example of Moving Annual Total
5. (03:30) Why Power Query and not Excel or DAX?
6. (03:43) Look at final solution and Custom Function to see what we are trying to accomplish, including a method to filter a table with in a Custom Column in Another Table and have the formula see criteria from the the Inner Table and the Outer Table.
7. (05:37) Step 1: Look at how we imported files
8. (06:07) Step 2: Extract a Sorted Unique List from the source Facet Table. Use Production Operator to get a List, then use the Table.Distinct and Table.Sort functions.
9. (07:31) Step 3: M Code to create a Crossjoin of all combinations of Months and Product Names with the steps: Extract Column, Convert to Start of Month, Extract Min and Max Dates, use List.Dates function to create range of dates, then merge using Custom Column to get all combinations of Months and dates.
10. (14:39) Step 4: Group BY Date and Product to get Monthly Totals.
11. (16:25) Step 5: Create Final Table with the steps: Merge Step 3 and Step 4, Remove Nulls, Add Custom Column to get One Year Back.
12. (20:15) Step 5: Sort and how it is different than Excel Sport.
13. (21:25) Step 5: Table.Buffer Function allows us to Buffer the Internal Table to prevent a call to the source table for every row in the table.
14. (22:22) Step 5: create Custom Column with Function to Calculate Moving Annual Totals (MAT).
15. (28:41) Add new data to test if everything updates
16. (29:06) 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 09 Power Query Complete M Code Introduction: Values, let, Lookup, Functions, Parameters, More

Posted on by Annie D

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.

View on YouTube

MSPTDA 08.5: Power Query Group By Unique List or Consecutive Occurrences

Posted on by Annie D

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.

View on YouTube

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

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

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