Ravindra BagaleCourses & study guides मराठी Track your progress

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

4.2 Connecting to Microsoft SQL Server

By Last updated:

Quick answer: the short, self-contained version of this lesson is the guide Connect SQL Server to Power BI (Import vs DirectQuery).

Start with a problem

Ravindra's orders now live in a SQL Server database, not in CSV files. He needs them in Power BI without copying files by hand.

Explain simply

Get data › SQL Server asks for a server, an optional database and a mode (Import or DirectQuery), then credentials. The Navigator lets you pick tables; Transform Data lets you keep only what you need.

Show the data

Table orders (created by quickcommerce_practice.sql):

order_id order_date store_id platform category amount delivery_mins status
301 2026-04-06 S01 Blinkit Dairy 400 8 Delivered
302 2026-04-06 S02 Amazon Now Snacks 200 12 Delivered
303 2026-04-07 S03 Blinkit Dairy 300 14 Delivered
304 2026-05-04 S04 Blinkit Fruits 150 9 Delivered
305 2026-05-04 S05 Amazon Now Snacks 450 7 Delivered
306 2026-05-05 S01 Blinkit Fruits 250 16 Delivered
307 2026-05-05 S04 Amazon Now Dairy 350 11 Delivered
308 2026-05-05 S05 Blinkit Dairy 0 NULL Cancelled

About this data: This table uses columns: order_id, order_date, store_id, platform, category, amount, delivery_mins, status. This table uses columns: store_id, store_name, city, region. This table uses columns: Queries. Values shown are the lesson examples already on this page (not a new dataset).

Table dark_store:

store_id store_name city region
S01 Kothrud Pune West Maharashtra
S02 Baner Pune West Maharashtra
S03 Gangapur Nashik North Maharashtra
S04 College Road Nashik North Maharashtra
S05 Rajarampuri Kolhapur South Maharashtra

Fictional sample data. Practice file: quickcommerce_practice.sql (source: src/practice/).

Demonstrate

  1. Install SQL Server Express, create database QuickCommerceDB, and run the practice script quickcommerce_practice.sql in it.
  2. Desktop › Home › Get data › SQL Server. Server localhost\SQLEXPRESS (or your server, e.g. QCSERVER01), Database QuickCommerceDB, mode Import › OK.
  3. Credentials: Windows › Use my current credentials › Connect. (If you see an encryption warning, ask your admin; do not just switch encryption off.)

  4. Navigator: tick dbo.orders and dbo.dark_store › Transform Data.

    Your result should look like this:

    Queries
    orders (8 rows) · dark_store (5 rows)

  5. In orders, keep the columns you need › Close & Apply › relate dark_store[store_id] 1 → * orders[store_id].

    Your result should look like this:

    Model
    2 tables, 1 relationship

Show the result

Your result should look like this (fictional sample):

City (dark_store) Total Sales (₹)
Pune 850
Nashik 800
Kolhapur 450
Total 2,100

Explain what happened

Power Query wrote Sql.Database("localhost\SQLEXPRESS", "QuickCommerceDB") and navigated to the two tables. Import copied 13 rows (8 + 5) into the model; the relationship lets city totals work.

Explore further

  • Ask the database team for a read-only login and, ideally, a view that already filters what you need.

Detailed reference for this lesson:

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

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 only 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.

Fix common mistakes

Wrong result Reason Correction
Cannot connect to localhost Named instance not given Use localhost\SQLEXPRESS (or server,port)
Login failed Wrong authentication tab Windows for your login; Database for a SQL login such as pbi_reader
40 columns loaded Ticked tables with all columns Remove unused columns in Transform Data

Practise independently

Practice task

After loading, what is the total of delivered orders only, and how many rows were imported in total?

Hints
  • Order 308 is cancelled with ₹0.
Solution

Delivered total ₹2,100 (308 adds ₹0) across 7 delivered orders; 13 rows imported (8 orders + 5 stores).

Think like a professional

Connect with the least privilege you need: a read-only login and only the tables and columns your report uses.

Ravi Sir's Cards for this lesson

Connect to Microsoft SQL Server. Big companies keep sales data in databases, not Excel files. Power BI can read it directly.Revision card

मराठी cards