Databases, Cloud Services and APIs
Leaf & Cup is growing. Online orders now land in a SQL Server database on a machine in the back office, loyalty points live in a cloud CRM, and Meera wants public holidays marked on the sales chart, which a free web API can supply. Three very different sources — but Power BI asks the same three questions of each.
The rule
Every source needs an address, a credential and a route from the Service.
Databases — SQL Server, PostgreSQL, MySQL, Oracle and many more.
- Address: a server name and a database name.
- Credential: a database login, or your Windows or organisational account.
- The Navigator lists the tables and views; you tick the ones you need. Under Advanced options you can type your own SQL query instead, but picking tables lets Power Query push later filter steps back to the database, which is usually faster.
Cloud services — Azure SQL Database, Snowflake, Google BigQuery, Salesforce, Dataverse, SharePoint Online lists and so on.
- Address: an account name or a URL, usually chosen from a dedicated connector.
- Credential: most often you sign in with your organisational account.
- Because both ends are in the cloud, the Service can usually refresh them with no gateway.
APIs — any web service that answers a URL with data, reached with the Web connector.
- Address: the URL. Credential: anonymous, or an API key sent in a header or the query string.
- Most APIs answer with JSON, which Power Query shows as nested records (one object) and lists (many values). You turn the list into a table and expand the records into columns.
- Many APIs return data a page at a time, so one call may not bring everything.
let
Source = Json.Document(Web.Contents("https://api.example.com/holidays?country=IN&year=2026")),
Holidays = Table.FromRecords(Source)
in
HolidaysThe route decides refresh. A database on an office server is on-premises, so the Service needs an on-premises data gateway to reach it. Cloud services and public APIs are reached directly.
The shortcut
For every new source, fill in three blanks: Address? Credential? Route? If the route answer is "inside our office network", write gateway next to it before you publish.
Online orders in SQL Server
Address: server
BACKOFFICE01, databaseOrders. Credential: a database login. Route: office network, so a gateway is needed.
The trap is treating an API like a table. JSON arrives nested, so each level must be expanded, and a paged API that returns 100 rows per call gives you only the first 100 rows until you request the other pages.
Drills
Answer fast — trust the rule.
What does the SQL Server connector ask for first?
The Orders database runs on a server in the Leaf & Cup back office. What does the Service need in order to refresh it?
Which source can usually be refreshed from the Service without a gateway?
A web API returns JSON. What do you see first in Power Query?
The holiday API returns 100 rows per page, and there are 250 holidays. How many rows does a single call to the URL bring back?
Remember
- Every source needs an address, a credential and a route from the Service.
- Fill in the three blanks; an office-network route means a gateway.
- Trap: JSON is nested and APIs are often paged — expand every level and fetch every page.