Tag: How to

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

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

Posted on by Annie D

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.

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

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

Posted on by Annie D

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

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

Excel Magic Trick 1510: Conditional Format Row With Duplicates Based on Product & Color

Posted on by Annie D

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

View on YouTube

Excel Hash Iron Chef Tournament: Your Secret Ingredient for #ExcelHash ?

Posted on by Annie D

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!

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

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