4.2 Connecting to Microsoft SQL Server

Steps in Power BI

1. Home › Get data › SQL Server (or Get data › More… › Database › SQL Server database › Connect).
2. Server: QCSERVER01 (for a named instance use QCSERVER01\SQLEXPRESS; for a specific port use QCSERVER01,1433).
3. Database (optional): QuickCommerceDB. If you fill it in, the Navigator opens directly at this database.
4. Data Connectivity mode: Import (recommended to start) or DirectQuery (see 4.6).
5. Leave Advanced options closed for now. They are covered in 4.3.
6. Click OK. In the credentials window, choose a tab on the left:
   • Windows – Use my current credentials (your Windows login) or Use alternate credentials.
   • Database – a SQL Server login, e.g. user pbi_reader and its password.
   • Microsoft account – for Azure SQL with Microsoft Entra ID (formerly Azure Active Directory).
7. Select the level these settings apply to (the server, or server + database) › Connect. If you see an encryption warning, speak to your database admin. Do not simply accept an unencrypted connection for sensitive data.
8. In the Navigator, tick Orders, DarkStore, Product, Customer, DeliveryPartner. The Select Related Tables button ticks tables linked by foreign keys automatically.
9. Click Transform Data (not Load) so you can remove columns you do not need, rename columns and set types.

let
    Source   = Sql.Database("QCSERVER01", "QuickCommerceDB"),
    Orders   = Source{[Schema = "dbo", Item = "Orders"]}[Data],
    Kept     = Table.SelectColumns(Orders, {"OrderID", "OrderDateTime", "CustomerID", "StoreID",
                  "ProductID", "Quantity", "Amount", "Discount", "DeliveryFee", "DeliveryTimeMins",
                  "OrderStatus", "PaymentMode", "Platform"}),
    Recent   = Table.SelectRows(Kept, each [OrderDateTime] >= #datetime(2025, 1, 1, 0, 0, 0))
in
    Recent