Web Source not pulling

[Bmejia]

Hi,
I am having issues extracting the last line from a web source table.   The last line is the current date exchange date.  When you copy the URL below it will give you the data, that I am pulling without the current date.  The only way to see the current date online is to click on "Retrived Data" which is the same URL below.

 

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 data

I 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.

 

See attachment

https://www.ofx.com/en-ie/forex-news/historical-exchange-rates/cad/usd/

What is capturing:

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

[Aala_Ali · Accepted solution]

If I understand you right your Problem is that
OFX 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 Canada
Pull the official USDCAD rate and invert to CAD→USD. Paste this M in a blank query:

let

  StartDate   = Date.ToText(Date.AddDays(Date.From(DateTimeZone.FixedUtcNow()), -15), "yyyy-MM-dd"),

  Url         = "https://www.bankofcanada.ca/valet/observations/FXUSDCAD/json?start_date=" & StartDate,

  Clean =

    let

      Source = Json.Document(Web.Contents(Url)),

      T0 = Table.FromList(Source[observations], Splitter.SplitByNothing(), {"obs"}),

      T1 = Table.ExpandRecordColumn(T0, "obs", {"d","FXUSDCAD"}, {"Date","FXUSDCAD"}),

      T2 = Table.ExpandRecordColumn(T1, "FXUSDCAD", {"v"}, {"USDCAD"}),

      T3 = Table.TransformColumnTypes(Table.SelectRows(T2, each ([USDCAD] <> null)),

                                      {{"Date", type date}, {"USDCAD", type number}})

    in

      T3,

  Last = Table.FirstN(Table.Sort(Clean, {{"Date", Order.Descending}}), 1),

  Result = Table.AddColumn(Last, "CADUSD", each 1/[USDCAD], type number)

in

  Result

This 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"

[Bmejia]

Thank You Aala Ali, 
I refreshed the report today and it seem the provider fix the issue, where you no longer need to click on "Retrived Data". 

The 1s recommendation was causing the same issue yesterday as I did try to look at it from a daily, monthly, yearly view. , so I would have probably would have gone with your second option at least to get the daily results.

Thanks again for taking the time, I really appreciated.