 Average Last 3 Customer Sales: Power Query, DAX Measure or Worksheet Formula

Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to Average a customers last three sales (last three dates) using Excel Worksheet Formulas, Power Query M Code and a Power Pivot & Power BI DAX Measure.
Topics:
1. (00:07) Introduction
2. (00:50) Sort Method
3. (02:09) Excel Worksheet Formulas, including AGGREGATE and AVERAGEIFS functions.
4. (05:27) LARGE Function and New Excel Office 365 Calculation Engine
5. (08:15) Power Query M Code, including Table.Sort, List.Average and List.FirstN M Code functions
6. (12:46) Power Query Parameter Query with condition coming from Excel Worksheet
7. (14:05) Power Pivot & Power BI DAX Measure, including TOPN, CALCULATE and AVERAGE DAX Functions
8. (17:08) DAX Parameter from Excel Worksheet using VALUES DAX Function.
9. (18:43) Add new records to table and test formulas
10. (19:00) Fix Power Query Table.Sort bug using Dummy Insert Column
11. (20:08) Fix Power Query Table.Sort bug using Table.Sort function inside Table.Group Function
12. (22:12) Conclusion

Using Locale in Power Query Power BI: Import & Append Text Files from Different Countries – MSPTDA 12

Download Zipped Folder with Text Files: https://ift.tt/2pwvCRa
Download Power BI Desktop FINISHED File: https://ift.tt/2psTK74

Comprehensive video about using Locale Settings so that Power Query interprets Dates and Numbers from different parts of the world correctly. In this Video learn about how to use the “Using Locale…” Feature and Regional Settings to import Text Files from Different Countries so that Dates and Numbers in Different Formats can be interpreted correct, and the multiple Text Files and be Appended into a single table. Also see how to change the Locale settings on individual columns.

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

Topics:
1. (00:15) Introduction
2. (00:25) Text Files from Different Countries have Different Date and Number Formats
3. (02:40) Change Regional Settings in Power Query and Power BI Desktop
4. (04:28) Using Locale… Feature on Single Columns to interpret Dates and Numbers Correctly
5. (06:50) Convert ISO Dates to Proper Dates in Power Query
6. (08:04) Power BI Desktop: Import Multiple Text Files with Different Date and Number Formats From Folder and Append. See 1) Create Table in Power BI Desktop, 2) Build Custom Function 3) Import Text Files From Folder and Append
7. (20:30) Summary

Assigned Homework:
Download pdf file with homework description: https://ift.tt/2psyxu0
Zipped Text Files: https://ift.tt/2I9op1C
Example of Finished Homework in Excel: https://ift.tt/2pvzUZ5

Which Power Query Steps Are Used in SQL Query Folding? “View Native Query” feature! – MSPTDA 11.5

Download Power BI Desktop FINISHED File: https://ift.tt/2O4YUkv

In this Video discusses the new “View Native Query” feature in Power BI Desktop Power Query and Office 365 Excel Power Query to determine which of the Applied Steps are sent back to the SQL Server Database as part of Query Folding.

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

Power Query to Import from SQL Server Database in Excel or Power BI Desktop – MSPTDA 11

Download Power BI Desktop FINISHED File: https://ift.tt/2O4YUkv
Practice Problems: Assigned Homework:
Download homework file (Practice Problems) : https://ift.tt/2O5PODZ
Example of Finished Homework: https://ift.tt/2O5PODZ

In this Video learn how to connect to an SQL Server Database and extract and transform data using Power Query in Excel and Power BI Desktop.

Topics:
1. (00:16) Introduction
2. (00:32) What is an SQL Server Database
3. (02:19) The Goal of our Queries and a look at the end result reports in Excel
4. (03:04) Comparing and Contrast using 1) Using Power Query User Interface or 2) Writing SQL Code in Power Query
5. (04:46) Example 1: Use Power Query User Interface to connect to SQL Server and Extract, Transform and Load Data.
6. (11:27) Example 2: Write SQL Code to connect to SQL Server and Extract, Transform and Load Data.
7. (14:44) Example 3: Using Power BI Desktop to connect to SQL Server and Import multiple Tables.
8. (18:29) Summary

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

Formula.Firewall Error in Power Query & Power BI: Rebuild This Data Combination Solved (MSPTDA 9.5)

Learn how to deal with Power Query Error: Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Two solutions are presented in this video.
Zipped Folder: https://ift.tt/2PShOvY
Assigned Homework – these are problems for you to practice your new M Code skills:
Example of Finished Homework: https://ift.tt/2L7OxtS

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

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

Download Power BI Desktop START File: https://ift.tt/2BXmQEV
Download Power BI Desktop FINISHED File: https://ift.tt/2MZj7e5
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.

MSPTDA 09 Power Query Complete M Code Introduction: Values, let, Lookup, Functions, Parameters, More

Assigned Homework:
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.

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

Assigned Homework:
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.

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

https://ift.tt/2veJ0vt
https://ift.tt/2LvyjQd
Assigned Homework:
Assigned Homework:
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.

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

https://ift.tt/2LwCF91
https://ift.tt/2JLJr5F
Assigned 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.