Power BI Advanced Series · Module 1 Summary · by Raushan Ranjan, MCT

Three lessons down. Before you move into Module 2 — where Power Query gets serious — this recap cements what Module 1 actually taught you. These are not just bullet points. They are the exact concepts the PL-300 exam tests, the ones interviewers ask about, and the foundation every subsequent lesson assumes you already know.

Module 1 in One Sentence Module 1 answered the question: how does data get into Power BI? — covering why data analysis matters, the 100+ source connectors available, the three connectivity modes and their trade-offs, and the six most common import errors you will encounter in production.
  • Power BI's data lifecycle: Acquire → Transform → Model → Analyse → Visualise → Share
  • Three connectivity modes: Import (fast, needs refresh), DirectQuery (live, source-speed limited), Live Connection (reuses published model)
  • Import errors fall into four categories: path errors, load errors, web auth errors, and refresh errors
  • Power Query is the transformation layer — fix data here, not in Excel before import
📖 Lesson 1 Recap — Getting Started with Power BI

Why data analysis? Data analysis converts raw numbers into decisions. The four types of analysis — Descriptive (what happened), Diagnostic (why it happened), Predictive (what will happen), Prescriptive (what should we do) — define the progression from basic reporting to full BI maturity.

The Data Analyst's workflow in Power BI:

1. Acquire
Connect to data sources via Power Query
2. Transform
Clean, shape, and merge data in Power Query Editor
3. Model
Define relationships, hierarchies, and calculated tables
4. Analyse
Write DAX measures and KPIs
5. Visualise
Build reports and dashboards in Power BI Desktop
6. Share
Publish to Power BI Service, configure security and refresh

← Revisit Lesson 1 →

📖 Lesson 2 Recap — Getting Data from Multiple Sources

Source categories: Files (Excel, CSV, JSON), Databases (SQL Server, PostgreSQL, Oracle), Azure / Cloud (Azure SQL, Synapse, Databricks), Online Services (SharePoint, Dynamics 365, Salesforce), Power Platform (Dataflows, Dataverse), Other (Web, OData, ODBC).

Connectivity modes — the decision you must get right before building anything:

ModeData LocationFreshnessUse When
ImportCopied into modelUp to last refreshPerformance-critical; complex DAX; data < 1GB
DirectQueryStays at sourceLive per interactionNear-real-time needed; large tables; governance
Live ConnectionPublished PBI / SSAS modelInherits from sourceReusing a certified shared dataset

← Revisit Lesson 2 →

📖 Lesson 3 Recap — Resolving Data Import Errors

Import errors fall into four predictable categories. Knowing the category narrows the fix immediately:

Error TypeTypical CauseFirst Fix to Try
Path ErrorFile moved, renamed, or hardcoded local pathUpdate source path; move file to SharePoint/OneDrive
Load ErrorData type mismatch; null in non-nullable column; schema changedCheck data types in Power Query; use Replace/Remove Errors steps
Web Auth ErrorWrong auth method; expired credentials; privacy level conflictRe-enter credentials; adjust privacy level setting
Refresh ErrorGateway offline; expired cloud credentials; source unreachableCheck gateway status; re-enter data source credentials in Service

← Revisit Lesson 3 →

✅ Module 1 — 5 Things to Lock In Before Module 2
  • The six-step Power BI workflow (Acquire → Transform → Model → Analyse → Visualise → Share) is the mental model for every task you will do
  • Import mode is the right default — only switch to DirectQuery when you have a specific reason
  • Native connectors (SQL Server, SharePoint) are always better than ODBC — they enable query folding
  • Fix data in Power Query, never in the source file before import — transformations must be repeatable
  • When a refresh fails in Power BI Service, check the gateway first, then credentials — in that order
🧠 Module 1 Exam-Style Quiz

Q1. A report author needs to combine sales data from SQL Server (10M rows, updated hourly) with a small static Excel product dimension (500 rows). What connectivity mode should each source use, and why?

Show Answer

SQL Server → DirectQuery (hourly updates require near-real-time freshness; 10M rows may exceed practical Import refresh limits).
Excel product dimension → Import (500 rows, static data — Import gives fast query response with no latency cost).
This is a Composite Model — Power BI supports mixing Import and DirectQuery in a single report. The Excel dimension is cached in memory; the SQL table is queried live.

Q2. After publishing a report, stakeholders report seeing yesterday's data despite the source database being updated this morning. What is the most likely cause, and what is the fix?

Show Answer

The dataset is using Import mode and the scheduled refresh has not run yet (or was not configured). Import mode snapshots data at refresh time — it does not reflect source changes between refreshes.
Fix: configure a scheduled refresh in Power BI Service to run at appropriate intervals (up to 8×/day on shared capacity, 48×/day on Premium). If truly real-time data is required, switch to DirectQuery.

Q3. A Power Query step shows a red error icon. The error message reads "DataFormat.Error: We couldn't convert to Number." What caused this and how do you fix it?

Show Answer

A data type mismatch — Power Query tried to cast a column to Number but found text values (e.g., "N/A", blank strings, or currency symbols like "$1,200").
Fix: in Power Query Editor, find the step applying the Number type change. Add a Replace Values step before it to clean non-numeric strings, or use Transform → Replace Errors to substitute a default value (e.g., null or 0) for rows that fail conversion.

Up Next — Module 2 Power Query Editor deep dive — the interface, Applied Steps panel, query folding, and your first real M transformations. Start Module 2 →
📚 Resources