Search code examples
excelpowerbipowerquerypowerbi-desktopm

Lookup the latest value from different query into a new column

I'm learning PowerQuery and PowerBI by making a small stock market app.

Excel File - Here

I'm facing the following issue -

I've the following Queries -

enter image description here

StockHistoricData has the last year's data of all the stocks I want to track on a day-on-day basis.

Tradebook has all the stocks that I've traded (buy/sell) (which may not be all present in StockHistoricData)

I want to make a custom column in Tradebook with the name CurrentPrice that fetches the latest price(StockHistoricData[ltp] based on StockHistoricPrice[Date].

For eg. if Tradebook has Tradebook[Symbol] as INFY then I want to get the StockHistoricData[ltp] for 12-Jan-2024.

I tried it using List.PositionOf but it keeps on connecting to the website I've fetched the data from rather than using the already fetched data. Is there a way to stop this?

Solution

  • Power Query will always stream data from the source - that is just the way it works. If query B depends on query A then whenever you refresh query B, data will be restreamed for query A too. In your case, if query A is a website and you want to avoid restreaming that data, you can load the result of query A to an Excel table and save it. Then update query B to get data from the now static query A Excel file.

    Ben Gribaudo has a good article on how this works: https://bengribaudo.com/blog/2023/03/03/7292/lazy-streamed-immutable-try-building-a-table#more-7292

    Relevant quote:

    In M, a table isn’t stored in memory as a fully materialized set of row and column values. Rather, a table is represented as a programmatic construct that contains a handle which can be used to request that the promised rows be produced on demand, one at a time (the technical term for this behavior is streaming). The expressions defining those rows and columns will then be evaluated only if their values are actually needed (lazy evaluation).