Web Source not pulling

Question

Hi,I am having issues extracting the last line from a web source table.&nbsp; &nbsp;The last line is the current date exchange date.&nbsp; When you copy the URL below it will give you the data, that I am pulling without the current date.&nbsp; The only way to see the current date online is to click on "Retrived Data" which is the same URL below.
&nbsp;
I feel that I might need to do something to my code that will allow me to click on "Retrive data" prior to pulling the dataI have tried changing the web.browsercontents to web.contents and make sure that I am authenticated correctly in the report and browser. I have used this links for years and never had this issues so I assume the web provider changed something at their end as well.
&nbsp;
See attachment
https://www.ofx.com/en-ie/forex-news/historical-exchange-rates/cad/usd/
What is capturing:
&nbsp;

What I need it to capture:&nbsp; Only shows when I click on "Retrive Data"

&nbsp;

aala_ali · Accepted Answer

If I understand you right your Problem is thatOFX shows the latest row only after you click “Retrieve data” (client-side). Power Query can’t click it.So you can try this :Fix 1 (quick, no code change)Use the US locale page that renders the latest daily rows in static HTML:https://www.ofx.com/en-us/forex-news/historical-exchange-rates/cad/usd/Get Data → Web → pick the table → keep Date/Rate → sort Date desc → keep top 1.(Verify direction: USDCAD vs CADUSD; invert if needed.)Fix 2 (recommended, stable API) – Bank of CanadaPull the official USDCAD rate and invert to CAD→USD. Paste this M in a blank query:let&nbsp; StartDate&nbsp;&nbsp; = Date.ToText(Date.AddDays(Date.From(DateTimeZone.FixedUtcNow()), -15), "yyyy-MM-dd"),&nbsp; Url&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = "https://www.bankofcanada.ca/valet/observations/FXUSDCAD/json?start_date=" &amp; StartDate,&nbsp; Clean =&nbsp;&nbsp;&nbsp; let&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Source = Json.Document(Web.Contents(Url)),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; T0 = Table.FromList(Source[observations], Splitter.SplitByNothing(), {"obs"}),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; T1 = Table.ExpandRecordColumn(T0, "obs", {"d","FXUSDCAD"}, {"Date","FXUSDCAD"}),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; T2 = Table.ExpandRecordColumn(T1, "FXUSDCAD", {"v"}, {"USDCAD"}),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; T3 = Table.TransformColumnTypes(Table.SelectRows(T2, each ([USDCAD] &lt;&gt; null)),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {{"Date", type date}, {"USDCAD", type number}})&nbsp;&nbsp;&nbsp; in&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; T3,&nbsp; Last = Table.FirstN(Table.Sort(Clean, {{"Date", Order.Descending}}), 1),&nbsp; Result = Table.AddColumn(Last, "CADUSD", each 1/[USDCAD], type number)in&nbsp; ResultThis returns the latest business day with both USDCAD and CADUSD.Tip (single value)If you need just the last value in a measure:= Rates_Last[CADUSD]{0}If this helps, please mark as Accepted Solution and drop a 👍"Kudos"

Forum Discussion

Web Source not pulling

2 Replies

Related content

The data source web is missing credentials

Incremental Refresh with Appended Web Source

Publicación web

Data source

WEB source