Gemini can help write and troubleshoot Power Query M, but it does not fetch the rate for Excel. Excel does that through Web.Contents. This guide uses the anonymous latest endpoint, validates the response, and exposes source, data_updated_at, and market_session so a tracker never presents an unexplained number as live.
Web.Contents with RelativePath and Query, avoiding fragile URL concatenation.Use Gemini for the query, not as the data source
Gemini can turn a plain-English requirement into Power Query M, explain a step, and suggest a fix when a refresh fails. It cannot make the workbook’s rate current by itself. The workbook still needs a query that calls the API, parses the JSON, checks that the requested currencies are present, and keeps the observation metadata beside the numbers.
When prompting Gemini, include the public documentation URL and the exact response fields you expect. Never paste an API key, private workbook data, or customer information into the prompt.
Write a macro-free Excel Power Query M query that calls
https://exchangerate.dev/docs/api-reference
and the anonymous endpoint /v1/latest/USD?symbols=JPY,KRW,EUR.
Use Web.Contents with RelativePath = "v1/latest/USD" and
Query = [symbols = "JPY,KRW,EUR"]. Parse the JSON response,
validate that rates, sources, data_updated_at, and market_session
exist, then return columns Currency, Rate, Source,
DataUpdatedAt, and MarketSession. Do not add an API key or
invent fallback values. Keep HTTP errors visible.Create the Power Query
In Excel, open Data → Get Data → From Other Sources → Blank Query, then choose Advanced Editor. Replace the starter code with the query below. It uses a fixed base URL and passes the path and parameters separately. Microsoft documents RelativePath and Query as options for Web.Contents; the base URL also makes Power Query’s source and privacy handling easier to inspect (Microsoft documentation).
let
Response = Web.Contents(
"https://api.exchangerate.dev",
[
RelativePath = "v1/latest/USD",
Query = [symbols = "JPY,KRW,EUR"],
ManualStatusHandling = {429}
]
),
Status = try Value.Metadata(Response)[Response.Status] otherwise 200,
Checked = if Status <> 200 then
error "The FX endpoint returned HTTP " & Text.From(Status)
else Response,
Json = Json.Document(Checked),
Rates = try Json[rates] otherwise error "The response has no rates field",
Sources = try Json[sources] otherwise error "The response has no sources field",
UpdatedAt = try Json[data_updated_at] otherwise error "The response has no data_updated_at field",
Session = try Json[market_session] otherwise error "The response has no market_session field",
RequireRate = (rates as record, code as text) as number =>
let value = try Record.Field(rates, code) otherwise null
in if value is number and value > 0 then value else error (code & " is missing or invalid"),
RequireSource = (sources as record, code as text) as text =>
let value = try Record.Field(sources, code) otherwise null
in if value is text and Text.Length(value) > 0 then value else error (code & " source is missing or invalid"),
RequiredMetadata = if UpdatedAt is text and Text.Length(UpdatedAt) > 0 and Session is text and Text.Length(Session) > 0 then true else error "Required metadata is missing or invalid",
Rows = if RequiredMetadata then Table.FromRecords({
[Currency = "JPY", Rate = RequireRate(Rates, "JPY"), Source = RequireSource(Sources, "JPY"), DataUpdatedAt = UpdatedAt, MarketSession = Session],
[Currency = "KRW", Rate = RequireRate(Rates, "KRW"), Source = RequireSource(Sources, "KRW"), DataUpdatedAt = UpdatedAt, MarketSession = Session],
[Currency = "EUR", Rate = RequireRate(Rates, "EUR"), Source = RequireSource(Sources, "EUR"), DataUpdatedAt = UpdatedAt, MarketSession = Session]
}) else error "Required metadata is missing or invalid",
Typed = Table.TransformColumnTypes(Rows, {{"Currency", type text}, {"Rate", type number}, {"Source", type text}, {"DataUpdatedAt", type datetimezone}, {"MarketSession", type text}})
in
TypedLoad a table that explains each value
Choose Done → Close & Load To… → Table. A successful refresh should produce one row per requested currency. The rate means units of the quote currency for one USD. Source, DataUpdatedAt, and MarketSession describe the observation; they do not turn it into an executable quote.
Refresh the tracker
Use Data → Refresh All when you want Excel to run the query again. Refresh behavior, scheduled refresh, and available Power Query features vary by Excel edition, operating system, privacy settings, and organizational policy. Do not assume that a workbook refreshes automatically or that the same trigger options exist on Mac and Windows. Test the exact Excel runtime where the workbook will be used.
Troubleshoot visible failures
Know the boundaries
This macro-free workbook is a useful private tracker and model input. Anonymous access is capped at 10 requests a minute and 100 an hour per IP, and shared egress can make that capacity variable. If a shared workbook needs predictable account-level capacity, put the API key in a server-side service. Keep the key out of Power Query, workbook parameters, prompts, and version control. The returned values remain indicative and are not for settlement.