Connecting Power BI to Various Data Sources - Textnotes

Connecting Power BI to Various Data Sources

This module teaches how to connect Power BI to multiple data sources. Learners will understand the differences between Import and DirectQuery modes, explore structured and unstructured data sources, and practice loading and previewing data to build the foundation for reports and dashboards.

1. Connecting to Excel, CSV, and Other Flat Files

Power BI can directly connect to Excel files (.xlsx), CSV files, and other flat files. This is ideal for small datasets or initial reporting.

Example:

  1. An Excel sheet contains monthly sales:
ProductSales AmountMonth
A100000Jan
B80000Jan
  1. Connect the Excel file to Power BI Desktop and load it as a dataset.

2. Connecting to Databases: SQL Server, MySQL, Oracle

Power BI can query live or imported data from relational databases.

  1. SQL Server: Enter server name and database, choose Import or DirectQuery
  2. MySQL/Oracle: Use appropriate credentials and connection parameters

Example:

  1. A SQL Server database stores order details. Import the “Orders” table to analyze sales trends.

3. Connecting to Azure Data Sources: Blob Storage, SQL Database

Azure data sources are cloud-based. Power BI can connect to:

  1. Azure Blob Storage: Load files stored in cloud
  2. Azure SQL Database: Query live data using DirectQuery

Example:

  1. A company stores monthly logs in Azure Blob Storage. Power BI reads the CSV files directly for reporting.

4. Connecting to Web APIs and JSON Files

Power BI can consume data from web APIs or JSON files for real-time or external data integration.

  1. Enter API URL in Power BI
  2. Use authentication if required
  3. Parse JSON into tables for reporting

Example:

  1. Connect to a weather API to get daily temperature data for multiple cities.

5. Connecting to SharePoint Lists/Files

Power BI can access SharePoint Online or On-Premises for lists or files.

  1. Enter SharePoint URL and select the file or list
  2. Load it as a dataset

Example:

  1. Connect to a SharePoint list containing employee attendance records for HR reporting.

6. Connecting to Power BI Datasets

You can reuse existing datasets from Power BI Service:

  1. Connect to published datasets using “Get Data → Power BI Datasets”
  2. Ideal for team collaboration and consistent metrics

Example:

  1. Use a shared “Sales Dataset” to build multiple reports without importing data repeatedly.

7. Import vs DirectQuery

Import Mode:

  1. Loads data into Power BI
  2. Fast for calculations
  3. Data refresh required to update

DirectQuery Mode:

  1. Queries the source database in real-time
  2. No data is stored in Power BI
  3. Best for large datasets or live dashboards

Example:

  1. Import: Sales data from Excel for monthly reporting
  2. DirectQuery: Live SQL database of transactions for daily monitoring