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