Refresh Trends API in Power BI

Power BI refreshes Trends API in two ways. Power Query Web.Contents POSTs https://api.trendsapi.ai/api and parses body twice. Or a dataflow or Desktop import reads a BigQuery table that a loader already filled. The second path keeps the Bearer key out of the .pbix. Excel uses the same M, on the Excel page. Fields: the API reference. Caps: pricing.

Web.Contents from Desktop or a dataflow

let
  raw = Web.Contents("https://api.trendsapi.ai/api", [
    Headers = [
      Authorization = "Bearer " & apiKey,
      #"Content-Type" = "application/json"
    ],
    Content = Text.ToBinary("{""mode"":""get_time_series"",""source"":""google search"",""keyword"":""bitcoin""}")
  ]),
  envelope = Json.Document(raw),
  points = Json.Document(envelope[body])
in
  Table.FromRecords(points)

A dataflow holds apiKey as a credential. Publishing a PBIX with a literal token is a leak. Official product docs say not to ship the key in a public file.

Scheduled refresh on a dataset that Web.Contents on every open will spend 200s. Prefer one dataflow, many reports.

Warehouse import when reports share rows

If Airflow or pandas already lands series and growth tables, Power BI imports those. No header. No second parse. Looker can sit on the same tables, on the Looker page. The POST then happens once per keyword in the loader, not once per report.

Two visuals

A line chart of value by date is get_time_series. A card or bar of growth by period is get_growth. Do not plot them as one series named "trend." Inner shapes are on get_time_series and get_growth. The double parse, when Web.Contents is used, is on parse the body.