Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

Database Data Sources

Learn how to connect Power BI Desktop to relational databases, select tables and views, understand Import and DirectQuery storage modes, configure database connections, and troubleshoot common connection problems.

1. Learning Objectives

Organizations commonly store business information in databases. Sales transactions, customer details, inventory, finance records, and operational information may all be maintained in relational database systems.

By the end of this lesson, you will be able to:

  • Understand the role of databases in Power BI.
  • Identify common relational database sources.
  • Connect Power BI Desktop to a supported database.
  • Understand server and database connection details.
  • Select tables and views for reporting.
  • Compare Import and DirectQuery storage modes.
  • Recognize authentication and permission requirements.
  • Troubleshoot common database connection problems.

2. Understanding Database Data Sources

A database is an organized collection of data managed by a database management system. Relational databases store information in tables containing rows and columns, and related tables can be connected using key columns.

Power BI Desktop provides connectors for various database technologies. The available connectors and supported features depend on the database system, Power BI version, drivers, permissions, and connection method.

Database Source Typical Use Example Data
Microsoft SQL Server Enterprise applications and business reporting. Orders, customers, products, and sales.
Azure SQL Database Cloud-hosted relational data. Application and operational records.
MySQL Web applications and business systems. Users, transactions, and inventory.
PostgreSQL Applications and analytical databases. Operational and reporting data.
Oracle Database Enterprise and large-scale business systems. Financial and operational records.
Important: The database connector must be compatible with your database system. Some sources may require additional drivers, network access, gateway configuration, or administrator permissions.

3. How Power BI Connects to a Database

Power BI uses a database connector to establish a connection to the selected source. Depending on the storage mode and connector, data may be imported into the Power BI model or queried from the source when users interact with a report.

Database Tables and Views Source Records Connector Server and Database Authentication Power BI Load or Query Data Reports Charts Analysis Successful connections require valid settings and authorized access.

Simplified database connection workflow.

4. Information Required for a Database Connection

Before connecting to a database, obtain the correct connection information from the database administrator or authorized system owner.

Connection Detail Purpose Example
Server name Identifies the database server or endpoint. SERVER01
Database name Identifies the database to access. SalesDB
Authentication method Determines how the user or application is authenticated. Windows, database credentials, or supported organizational authentication.
Network access Allows the client to reach the database server. Approved network route and firewall rules.
Permissions Controls which data can be read. Read access to approved reporting tables.
Security Reminder: Never place real database passwords or sensitive connection details inside tutorial code, screenshots, public repositories, or report descriptions.

5. Connecting to Microsoft SQL Server

Microsoft SQL Server is a common source for Power BI reports. The following workflow describes a typical connection using Power BI Desktop and the SQL Server database connector.

Step-by-Step Procedure

  1. Open Power BI Desktop.
  2. Select the Home ribbon.
  3. Choose Get data.
  4. Select SQL Server database.
  5. Enter the server name provided by your administrator.
  6. Enter the database name if you want to specify a particular database.
  7. Select the appropriate data connectivity mode.
  8. Choose OK to continue.
  9. Select the supported authentication method and enter the required credentials.
  10. In Navigator, select the required tables or views.
  11. Review the data preview.
  12. Choose Load or Transform Data.
  13. Verify that the selected data appears correctly in Power BI Desktop.

Example Connection Information

The following values are illustrative placeholders. Replace them with the connection details supplied for your own database environment.

Server SERVER01
Database SalesDB
Table Sales
Authentication Use the method approved for the environment.
Best Practice: Start by connecting to a small, approved table or view. Confirm that the connection works before importing large datasets.

6. Selecting Tables and Views

After connecting successfully, Power BI may display a list of available database objects in Navigator. The exact list depends on the database, connector, and your permissions.

Navigator ▾ SalesDB ☑ Customers ☑ Products ☑ Sales ☐ InternalNotes Data Preview: Sales OrderID ProductID Quantity Sales 1001 P01 2 120000 1002 P02 3 45000 1003 P03 5 7500 Select only the database objects required for the report.

Illustrative Navigator layout showing selectable tables and a data preview.

Selection Guidelines

  • Choose tables or views relevant to the reporting requirement.
  • Review the available columns before loading data.
  • Prefer approved reporting views when provided by the database team.
  • Avoid importing unrelated or sensitive tables.
  • Confirm that the selected objects contain the expected records.
Important: The Navigator is a selection interface, not a replacement for data validation. Confirm the table's meaning and structure before building report visuals.

7. Import Mode and DirectQuery

Power BI supports different ways to access data. Two important modes for relational database sources are Import and DirectQuery. The appropriate choice depends on data volume, performance, freshness requirements, connector support, and model design.

Feature Import DirectQuery
Data storage Data is loaded into the Power BI model. Data generally remains in the source system.
Report interaction Usually queries the imported model. May send queries to the source as users interact with the report.
Data freshness Requires refresh to retrieve updated source data. Can reflect source changes when queries execute, subject to caching and configuration.
Performance factors Model size and design affect performance. Source performance, network latency, query design, and concurrency matter.
Typical use Analytics using data loaded into the model. Scenarios requiring source-side queries or specific data access patterns.
Practical Guidance: Import is a common starting choice for learning and many reporting scenarios. DirectQuery should be evaluated when source-side querying, freshness, or model constraints make it appropriate.

8. Understanding the Two Storage Modes

Import Mode Database Source Data Power BI Model Imported Data Refresh to retrieve source changes DirectQuery Database Source Data Report User Interaction Queries can reach the source during report interactions Actual behavior depends on the connector and configuration.

Conceptual comparison of imported model data and source-side querying.

9. Tables, Views, and SQL Queries

Database Tables

A table stores records in rows and fields in columns. For example, a Sales table may contain OrderID, ProductID, OrderDate, Quantity, and SalesAmount.

Database Views

A view is a database object that presents data through a defined query. Database administrators may create views that expose only the fields and records needed for reporting.

SQL Queries

Some database connectors allow users to provide a native SQL statement. Whether this is available and which storage modes support it depend on the connector and environment.

SELECT
    OrderID,
    OrderDate,
    ProductID,
    Quantity,
    SalesAmount
FROM dbo.Sales;

This example selects five columns from a hypothetical SQL Server table. The table name and column names must match your actual database schema.

Security and performance: Use only approved queries and objects. Avoid selecting every column when only a few are needed, and consult your database administrator before running expensive queries against a production system.

10. Preparing Database Data for Reporting

Database data may require additional preparation before it becomes suitable for analysis. Power Query can help filter records, remove unused columns, correct data types, and standardize values.

Recommended Preparation Steps

  1. Select only the tables and fields required for reporting.
  2. Inspect the preview and confirm the expected row structure.
  3. Verify date, text, whole-number, and decimal data types.
  4. Remove unnecessary columns when appropriate.
  5. Filter records using clearly defined business requirements.
  6. Check for unexpected null values and conversion errors.
  7. Review relationships between related tables.
  8. Apply changes and validate the results before building visuals.

Example: Sales and Products

Suppose the database contains a Sales table and a Products table. Sales contains ProductID, while Products contains ProductID, ProductName, and Category.

These tables may be related through ProductID. Confirm that the key values match and that the Products table has the appropriate uniqueness for the intended relationship.

11. Authentication and Database Permissions

A successful database connection requires both a reachable server and an authentication method accepted by that source. The available methods vary by database connector and organizational configuration.

Requirement Purpose What to Check
Authentication Verifies the identity used for the connection. Use an approved account and supported method.
Read permissions Controls which objects can be accessed. Confirm access to the required tables or views.
Network connectivity Allows Power BI to reach the server. Check network access, endpoint, and firewall rules.
Gateway requirements May provide access to on-premises sources after publishing. Check whether a gateway and appropriate mapping are required.
Credential management Controls stored credentials and authentication settings. Follow the organization's security policy.
Best Practice: Use the least-privilege access required for the report. A reporting account should not receive unnecessary permissions to modify or delete production data.

12. Database Connection Troubleshooting

If a database connection fails, identify the failure stage before changing settings. The cause may be the server address, network access, authentication, permissions, or a problem with the selected database object.

Can Power BI Reach the Server? Are Credentials Accepted? Are Required Objects Accessible? Check access and setup Validate data and continue No No No

A simplified troubleshooting sequence for diagnosing database connection issues.

Error or Problem Possible Cause Recommended Action
Server not found Incorrect server name or network problem. Verify the server address and network access.
Authentication failed Incorrect credentials or unsupported authentication method. Confirm the approved authentication method and account.
Access denied The account lacks permission to the requested object. Ask the database administrator to review access.
Database not found Incorrect database name or insufficient visibility. Verify the database name and account permissions.
Query is slow Large result set, expensive query, or source performance issue. Reduce unnecessary columns and rows; review query performance.
Published report cannot refresh Gateway, credential, or source configuration issue. Review the published semantic model's source and refresh configuration.

13. Hands-On Practice Assignment

Assignment: Connect to a Sample Database

  1. Use an approved training database or a sample database available in your environment.
  2. Obtain the server name and database name from your instructor or administrator.
  3. Open Power BI Desktop and select Get data.
  4. Choose the appropriate database connector.
  5. Enter the connection details.
  6. Select an approved authentication method.
  7. Open Navigator and inspect the available tables and views.
  8. Select one suitable table and review its preview.
  9. Choose Transform Data and inspect the columns.
  10. Verify the data types and remove unnecessary columns when appropriate.
  11. Apply the changes and validate the imported data.
  12. If supported, compare Import and DirectQuery behavior using a non-production test source.

Practice Questions

  • Which connector did you use?
  • Which server and database did you connect to?
  • Which tables or views were available?
  • What authentication method was approved for the connection?
  • Which columns did you select for reporting?
  • Did you identify any null values or data-type issues?
  • Which storage mode was suitable for your practice scenario, and why?

Completion Checklist

  • ☐ I understand what a database source is.
  • ☐ I can identify the required connection details.
  • ☐ I can connect to a supported database.
  • ☐ I can select tables or views in Navigator.
  • ☐ I understand Import and DirectQuery.
  • ☐ I can identify common connection errors.
  • ☐ I understand database access and credential security.

14. Knowledge Check

Question 1

What is a relational database?

Answer: A database that organizes data in related tables containing rows and columns.

Question 2

What information is commonly needed to connect to a database?

Answer: The server or endpoint, database name where applicable, authentication details, and sufficient permissions.

Question 3

What is the purpose of the Navigator window?

Answer: It allows users to inspect and select available tables, views, or other supported objects.

Question 4

What is Import mode?

Answer: A storage mode that loads data into the Power BI model.

Question 5

What is DirectQuery?

Answer: A mode in which supported report queries can be sent to the underlying data source rather than importing all source data into the model.

Question 6

Why are database permissions important?

Answer: They restrict access to authorized data and help protect sensitive information.

Question 7

What should you check if the server cannot be reached?

Answer: Verify the server name, network connectivity, endpoint availability, and any relevant firewall requirements.

Question 8

Why might a published report require a gateway?

Answer: A gateway may be needed to provide the Power BI service with secure access to an on-premises data source.

Summary

In this lesson, you learned how Power BI connects to supported database systems, how to enter connection details, and how to select tables and views through Navigator.

You also compared Import and DirectQuery, reviewed authentication and permission requirements, explored basic data preparation, and learned how to troubleshoot common connection issues.