Power BI Module 3: Connecting to Data (Excel, MySQL, MongoDB, JSON, Web APIs) – Complete Tutorial

🔌 Module 3: Connecting to Data

Power BI allows you to connect to nearly every type of data source — from structured databases like MySQL and Excel to unstructured document stores like MongoDB. Here’s how to handle each one efficiently.

📁 1. Excel – Most Common Starting Point

Excel remains one of the easiest and most popular data sources for Power BI. It’s ideal for quick analysis and prototyping.

• Click Home → Get Data → Excel
• Select a workbook (e.g., employee_performance.xlsx)
• Choose a sheet or named range
• Click Load or Transform to clean it in Power Query

💡 Tip: Use named tables in Excel for auto-detection of headers and relationships in Power BI.

🐬 2. Connect to MySQL Database

MySQL is widely used for web applications and internal business systems. Power BI connects easily using its native MySQL connector.

• Click Get Data → Database → MySQL
• Enter the server name (e.g., 127.0.0.1) and database name
• Provide credentials (username & password)
• Select tables or use a custom SQL query
• Click Load to import or Transform to use Power Query

🛠️ Requirements: Install the MySQL Connector/NET driver before connecting.

📌 Example: Use MySQL to pull order history, then create a sales trend chart in Power BI with slicers for year and category.

🍃 3. Connect to MongoDB (via ODBC or BI Connector)

MongoDB is a NoSQL document store used for flexible, schema-less data. Power BI doesn’t have a direct connector yet, but there are two reliable ways to connect:

Option 1: MongoDB BI Connector

• Install MongoDB BI Connector to expose Mongo collections as SQL tables
• Connect via MySQL connector (MongoDB BI uses MySQL protocol)
• Import collections and query like normal tables

Option 2: ODBC Driver

• Install MongoDB ODBC Driver (e.g., by CData or Simba)
• Set up an ODBC data source (DSN)
• Go to Get Data → ODBC in Power BI
• Select your DSN and browse collections

⚠️ Note: Ensure your MongoDB instance has stable schema for visualizing in Power BI. Flatten nested fields in Power Query.

💡 Use Case:

Visualize user activity logs or IoT device messages from a MongoDB collection. Use Power Query to expand nested JSON fields.

📌 Summary: Which Source for What?

🎯 Final Tip: Always use Power Query Editor to clean and transform data — especially when pulling from semi-structured formats like MongoDB or JSON.