Ravindra BagaleCourses & study guides

5. Web Data, APIs and Google Sheets

5.4 JSON APIs with Web.Contents and Json.Document

By Last updated:

Mitrano, many systems (courier tracking, weather, internal order services) offer REST APIs that return JSON. Here is a fictional internal endpoint that returns dark stores:

{ "stores": [
    { "storeId": "BLK-PUN-KOT-01", "area": "Kothrud",    "city": "Pune",   "lat": 18.507, "lng": 73.807 },
    { "storeId": "BLK-NGP-DHP-01", "area": "Dharampeth", "city": "Nagpur", "lat": 21.139, "lng": 79.063 } ] }

Steps in Power BI

  1. Get data › Web › Advanced › URL parts https://api.example.com/ and v1/darkstores › add a header Accept = application/json › OK.
  2. Choose the authentication the API needs. Anonymous for public APIs, or Web API (key) / Basic / Organizational account as documented by the provider.
  3. Power Query shows a Record. Click the List next to stores › List Tools › Transform › To Table › OK.
  4. Click the expand icon on Column1 › tick all fields › untick Use original column name as prefix › OK.
  5. Set types (lat/lng as Decimal Number) and set the Data category to Latitude/Longitude later in the model (Module 15).
let
    Source  = Json.Document(Web.Contents("https://api.example.com",
                 [RelativePath = "v1/darkstores", Headers = [Accept = "application/json"]])),
    Stores  = Table.FromRecords(Source[stores]),
    Typed   = Table.TransformColumnTypes(Stores, {{"lat", type number}, {"lng", type number}})
in
    Typed

Never paste secret API keys into a shared query

Anyone who opens the .pbix or Advanced Editor can read them. Use the connector's credential dialog (for example Web API key) so the key is stored in Power BI's credential store, or ask your admin for the approved method.

Practice task

Using any free public JSON API that allows anonymous use (read its terms first), load one list of records, turn it into a table and set the data types.

Ravindra Bagale's Tip

Many new learners expand every JSON record and list field, creating hundreds of columns. Expand only the fields you need and set data types immediately. Check the API's page size and rate limits before looping through many pages. Don't worry – after doing it two or three times, it becomes a habit.