MSPTDA 20: Query Parameter for Dynamic Folder Location / Data Source in Power BI & Excel Power Query

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

COUNTIFS in DAX for Power Pivot or Power BI? COUNTROWS Function. Excel Magic Trick 1550

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

In this video learn how to use the DAX Function COUNTROWS to simulate COUNTIFS in an Excel Worksheet. Use COUNTROWS to count with one or more conditions.

View on YouTube

COUNTIFS in Power Query? Excel Magic Trick 1549

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

Collapse The Colossal Excel Search Box – 2279

I have two great things to report about Excel. First, the old Tool Tabs in the Ribbon have become smaller. I love the new style and it makes perfect sense. Second, I have a bit of a rant about the questionable Microsoft Search box that is now dominating the Title Bar in Excel. Who is using this? Why are they making it so large? Great news part 2: If you plan to never use the Search box, minimize it to a tiny icon.

View on YouTube

Excel Table NOT Automatically Copy Formula Down Column? Option Setting to Fix This. Magic Trick 1548

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

Excel Table NOT Automatically Add New Column or Rows? Option Setting to Fix This. Magic Trick 1547

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 Excel Option Setting so that new columns and rows in an Excel Table are automatically added to the Excel Table Object:
Options setting: File, Options, Proofing, Autocorrect Options, AutoFormat As You Type, check the textbox named “Included New Rows and Columns in Table”.

View on YouTube

Power Query Get Previous Row? Stock Price Change Formula. M Code Lookup. Excel Magic Trick 1546

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

Create Random Data, Variable Height INDEX & RANDARRAY Dynamic Array Function: Excel Magic Trick 1545

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

In this video learn how to create a random table of text and number data that has a variable for number of rows using the INDEX Lookup Function and the Office 365 Dynamic Array Function RANDARRAY.

View on YouTube

Excel Magic Trick 1544: FILTER Array Function to Filter Between Upper & Lower Text Number Limits

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

Perplexing! Filter Text Between 6* and 9* – 2278

I can’t solve today’s question using the regular Filter. I am sure someone has a better way. A person in my Milwaukee seminar wants to filter text account numbers to everything between 6 & 9. Do you have a solution?

View on YouTube