Web Source not pulling

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"