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

Further Help

I offer limited consulting services to potentially assist you with data challenges, whether it's designing a complex Excel formula, writing a macro or building a whole new process for data capture, modeling and analysis.  Contact me if you have a need.