Ravindra BagaleCourses & study guides

4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection

4.2 Connecting to Microsoft SQL Server

By Last updated:

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

Ask for a read-only login and a view

He bagha: ask the database team for a read-only user and, ideally, a view that already joins and filters what you need (for example vw_MaharashtraOrders). Views are easy to reuse and keep query folding working.

Practice task

Install SQL Server Express (free) on your laptop, restore or create a small QuickCommerceDB with Orders and DarkStore, and connect in Import mode with Windows authentication. Load fakt the columns listed above.

Ravindra Bagale's Tip

A common mistake is ticking every table in the Navigator "just in case". Select only the tables and views you need and remove unused columns early. Importing a 40-column table when you need 8 makes refresh slow and the model heavy. Don't worry – after doing it two or three times, it becomes a habit.