All 6 files and folders and text files used in video are available in this zipped folder: http://bit.ly/2V31jnl
Download file individually at class web site: http://bit.ly/2GF25Ni
pfd notes for Video #20: http://bit.ly/2UY2mVs

In this video learn about how to create a variable or parameter for a data source path (folder path in this video) in Excel Power Query and in Power BI Desktop Power Query. Learn about Query References other queries or steps, so it may not directly access a data source Error in Power BI.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:23) Introduction
2. (02:36) Excel Power Query Example of Parameter for Data Source / Folder Path
3. (8;48) Power BI Example of Parameter for Data Source / Folder Path
4. (10:23) Summary

View on YouTube

Download Excel Start Files: http://bit.ly/2tSGcWC EMT1549-1550.xlsx
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to use the Power Query Group By feature to simulate the COUNTIFS function. See how to count with one condition, See how to Count with Two Conditions

View on YouTube

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

In this video learn how to change the default setting for copying formulas in Excel Tables down Calculated Columns.
Options setting: File, Options, Proofing, Autocorrect Options, AutoFormat As You Type, check the textbox named “Fill formulas in tables to create calculated columns”.

View on YouTube

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

In this video learn how to perform a two-way lookup to get the value from the previous row in order to calculate the change in stock price from day to day.

1. (00:10) Introduction
2. (00:48) Formula in Excel
3. (01:33) Two Way Lookup to Get Previous Row M Code Formula Syntax
4. (05:16) Add Index Column that starts at -1
5. (06:08) Two Way Lookup Power Query Formula, including looking at Table, Record and literal values in a column.
6. (10:08) Formula to calculate Stock Price Change
7. (10:36) Power Query if function
8. (12:13) Summary
Other notes:
Power Query is base 0
Two-Way Lookup: TableName{RowIndexNumber}[Column Name] Two-Way Lookup: TableName{[Lookup Operator]}[Column Name] Formula we use: AddedIndex{[Index]}[GEClose] AddedIndex = Full Table with Columns and Rows
{ } = Positional Index Operator
[Index] = Lookup Operator
[GEClose] = Field Access Operator

View on YouTube

Excel Magic Trick 1544: FILTER Array Function to Filter Between Upper & Lower Text Number Limits
Download Excel Start Files: http://bit.ly/2Ke4enW
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to use the new Office 365 (Insider Edition) FILTER Function to filter a data set based on a Lower Limit and Upper Limit Text Number.
This video is in response to Mr Excel’s video: https://www.youtube.com/watch?v=0UkUoyVBIws

View on YouTube