### 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

Assigned Homework:
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).
16. (29:06) Summary

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

### Excel Magic Trick 1513: COUNTIFS from Multiple Cells!?!? Array Formula or Logical Formula?

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.

### 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.

### Excel Magic Trick 1512: Count Workers Employed 1 to 6 Years Based on Hire Date? 9 Examples

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

### Introducing the Things I Have Learned Series (LPT)

Every day I am so fortunate to learn new things about others, the world, myself and so on.  I’ve decided that I’d like to start sharing some of that with you and the world.

Sometimes it’s some small fact, other times it’s something somewhat obvious that pretty much everyone else seemingly already knows.

I hope you enjoy the Things I Have Learnedseries, and I welcome your feedback and additions to this little knowledge base!

### 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.