Course: Data Connections in Power BI
Data connections in Power BI are the foundation of any reliable analytics solution because they determine where data comes from and how it flows into the model. Establishing robust connections to sources such as SQL Server, Excel, cloud services, or web APIs is the first step in building useful reports and dashboards. Once connected, data can be shaped in Power Query (M) and loaded into the model, or accessed directly via DirectQuery depending on performance and freshness requirements. DAX extends the model by enabling calculated tables and measures; for example, a calendar table can be created with Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey])). Summarization functions like SUMMARIZECOLUMNS allow you to group by attributes (month, category) and compute measures such as total orders and average profit per order. When authoring DAX queries, remember that report sort orders are not automatically applied to query output, so include ORDER BY columns explicitly and consider START AT for paginated retrieval. Storage modes (Import, DirectQuery, Composite) influence design decisions: use Import for speed, DirectQuery for real-time access, and Composite to mix both approaches. Incremental refresh and query parameters optimize refresh times for large, time-partitioned datasets. M language in Power Query is indispensable for pre-load transformations, merges, and append operations that keep the model lean and consistent. In the Power BI Service, published datasets enable collaboration, scheduled refresh, and centralized governance while maintaining the analytical logic developed in Desktop. Mastering connections, the M and DAX languages, and storage choices empowers analysts to build performant, maintainable solutions. Finally, practice with real schemas and iterate on model design to balance freshness, performance, and clarity in your reports.Lesson
Topic: Data Connections in Power BI — connecting data sources, modeling with DAX, and managing refresh and storage modes
Learning Outcomes
- Identify and configure common Power BI data sources (files, databases, cloud services, APIs).
- Create calculated tables and date tables using DAX.
- Summarize and retrieve data via DAX queries, handling ORDER BY and START AT.
- Differentiate Import, DirectQuery and Composite storage modes and choose appropriately.
- Apply M (Power Query) transformations before loading data into the model.
- Design incremental refresh policies and use query parameters to optimize performance.
Conceptual Explanation
Power BI connects to many kinds of data: relational databases (SQL Server, Oracle), flat files (Excel, CSV), cloud data stores (Azure, SaaS connectors) and REST APIs. The connection step lets you preview and transform source data in Power Query (M) before loading it into the data model. In the model, DAX is used to create calculated columns, measures and tables; a common pattern is to build a dedicated date table for robust time intelligence. For row- or table-level summaries, SUMMARIZECOLUMNS is effective; for ad-hoc tabular output you can use EVALUATE with ORDER BY and START AT clauses. Storage mode decisions—Import for fast in-memory queries, DirectQuery for live query against the source, and Composite to mix both—impact query performance and feature availability. Incremental refresh partitions data by date and refreshes only recent partitions, reducing load time for large datasets. Parameters enable dynamic queries and can be wired into incremental refresh to control RangeStart/RangeEnd. Finally, the Power BI Service provides collaboration, dataset governance, and scheduled refresh capabilities for Desktop-authored models.
Bilingual (EN → বাংলা)
English: Data connections in Power BI define how external data is brought into your model, transformed, and kept current. DAX and M work together—M handles extraction and transformation, while DAX handles modeling and calculations.
বাংলা: Power BI-তে ডেটা সংযোগ নির্ধারণ করে বাহ্যিক ডেটা কীভাবে মডেলে আনা হবে, রূপান্তরিত হবে এবং আপডেট রাখা হবে। M ও DAX একসাথে কাজ করে—M ডেটা আহরণ ও রূপান্তরের দায়িত্ব নেয়, আর DAX মডেলিং ও হিসাব করার কাজ করে।
Important Notes
- Always create a dedicated date table (Dim_Calendar) and mark it as Date Table in the model.
- Use DIVIDE() in DAX to avoid division-by-zero errors when computing averages or ratios.
- When running DAX queries for export or analysis, explicitly specify ORDER BY columns; model visual sort order is not applied.
- Choose storage mode based on dataset size, refresh frequency, and SLAs for freshness.
- Use Power Query (M) to trim and shape data before loading to reduce model size and improve performance.
Key DAX & M Examples
| Purpose | Example | Notes |
|---|---|---|
| Create calendar table (DAX) | |
Generates contiguous date rows between min and max dates. |
| Summarize by month & category (DAX) | |
Groups and computes measures; include filters and safe DIVIDE. |
| Export sorted table (DAX) | |
ORDER BY must be explicit; START AT for paging or focused retrieval. |
| Basic M query to load Excel table | |
M runs before load—use for filtering, merging, type changes. |
Exercises → True / False / Fill in the Blanks / MCQ / Matching / Descriptive / Creative
True / False
- Power BI automatically applies the visual sort order to DAX query outputs.
Answer
False — DAX queries do not inherit visual sort order; you must include ORDER BY explicitly.
- DirectQuery stores the entire dataset in-memory in Power BI.
Answer
False — DirectQuery queries the source on demand and does not import all data into Power BI memory.
- Using DIVIDE() is safer than using the division operator (/) when the denominator may be zero.
Answer
True — DIVIDE handles divide-by-zero by returning BLANK or a specified alternate result.
Fill in the Blanks
- To create a calendar table in DAX use the function ________.
Answer
CALENDAR
- In Power Query language the keyword used to create named steps is ________.
Answer
let ... in
- To refresh only recent partitions of a large table, configure ________ refresh.
Answer
Incremental
Multiple Choice Questions (MCQ)
- Which storage mode is best when you need the fastest query performance on a moderately sized dataset?
- A. DirectQuery
- B. Import
- C. Composite
Answer
B. Import — Import loads data into in-memory VertiPaq providing fastest query performance for many scenarios.
- Which DAX function helps avoid division by zero?
- A. DIV
- B. SAFE_DIVIDE
- C. DIVIDE
Answer
C. DIVIDE
Matching
Match the term to its purpose.
| Term | Purpose |
|---|---|
| Power Query (M) | A. Transform & shape data before load |
| DAX | B. Create measures, calculated columns, tables |
| Incremental Refresh | C. Refresh only changed partitions |
Answer
Power Query (M) → A; DAX → B; Incremental Refresh → C.
Descriptive
Explain briefly why you would create a dedicated date table in Power BI.
Answer
A dedicated date table guarantees continuous date ranges, supports robust time intelligence functions (YTD, MTD), allows consistent relationships to fact tables, and avoids ambiguous results when using time-related DAX functions.
Creative
Design a small model strategy (2–3 sentences) choosing storage modes and refresh approach for a daily sales dashboard with historical data and current-day updates.
Answer
Import historical sales data up to yesterday for high-performance aggregated analyses, and use DirectQuery or a separate DirectQuery table for today's transactions to provide near real-time visibility. Configure incremental refresh to update recent partitions daily and schedule nightly full refreshes for imported historical partitions as needed.
Activities, Vocabulary, Homework, Practice Task
Activities
- Connect to a SQL Server sample database and load the Sales table. Use Power Query to remove unused columns and change types.
Answer
Hint: Use Home → Get Data → SQL Server, filter/select columns in Power Query, then Apply & Close.
- Create Dim_Calendar using DAX and mark it as a Date Table.
Answer
Use Modeling → New Table with: Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey])) and then Mark as Date Table.
Vocabulary
- Dim_Calendar —
Answer
A date dimension table used for time intelligence.
- SUMMARIZECOLUMNS —
Answer
A DAX function to create grouped table results with aggregated measures.
- Composite Mode —
Answer
A mix of Import and DirectQuery storage modes in the same model.
Homework
Implement a small report: import monthly sales for the last two years, create measures for Total Sales and Avg Profit per Order, then publish to Power BI Service and configure a scheduled refresh.
Answer
Steps: (1) Import data into Desktop; (2) Create measures e.g. Total Sales = SUM('Sales'[SalesAmount]); Avg Profit per Order = DIVIDE([Total Sales Profit],[Orders]); (3) Publish to Service; (4) Configure dataset credentials and scheduled refresh in workspace settings.
Practice Task
Write a DAX query that summarizes Orders and Avg Profit per Order for "Clothing" by month and returns results ordered by Month of Year.
Answer
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Month Name],
'Date'[Month of Year],
'Product'[Category],
FILTER( VALUES('Product'[Category]), [Category] = "Clothing" ),
"Orders", [Orders],
"Avg Profit per Order", DIVIDE([Total Sales Profit], [Orders])
)
ORDER BY 'Date'[Month of Year] ASCFurther reading: Power Query M reference, DAX function reference, Power BI storage modes documentation, and articles on incremental refresh best practices.