Course (Part 742)

Course: Data Connections in Power BI

Lesson

Topic: Data Connections in Power BI

Learning Outcomes

• Understand Power BI connection types: Import, DirectQuery, Composite.
• Create calculated tables and a calendar table using DAX.
• Summarize and filter data with SUMMARIZECOLUMNS and related DAX functions.
• Configure incremental refresh and query parameters for large datasets.
• Distinguish roles of Power Query (M) and DAX in the ETL and modeling pipeline.
• Compose DAX queries with explicit ordering and starting points (ORDER BY, START AT).

Conceptual Explanation

Power BI connects to many data sources; the choice of storage mode affects performance and refresh options. Use Power Query (M) to perform transformations before loading and use DAX to build model-level calculations after loading. Calculated tables are useful for constructing dimensions such as date tables; measures and summarization enable row-level aggregation for reports. DAX queries executed via EVALUATE return tables and require explicit ORDER BY when deterministic ordering is needed. Incremental refresh combined with parameters reduces workload on refresh operations for growing tables. Below are compact examples and patterns you will reference.

Dim_Calendar =
CALENDAR(
  MIN('Fact_StrategyPlan'[Datekey]),
  MAX('Fact_StrategyPlan'[Datekey])
)

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])
)

EVALUATE
'Sales Order'
ORDER BY 'Sales Order'[Sales Order] ASC
START AT "SO43661"

Bilingual (EN → বাংলা)

• Data Connection — ডেটা সংযোগ
• Calculated Table — হিসাবকৃত টেবিল
• Calendar Table — ক্যালেন্ডার টেবিল
• Summarize — সারাংশ/সংক্ষেপ
• Incremental Refresh — ধাপে ধাপে রিফ্রেশ
• ORDER BY / START AT — সাজানো / নির্দিষ্ট শুরু পয়েন্ট

Important Notes

• ORDER BY must be included in DAX queries to enforce a specific sort order.
• Power Query (M) transformations run before DAX calculations—design accordingly.
• Choose storage mode based on latency, dataset size, and refresh needs.
• Use DIVIDE() to avoid division-by-zero errors in measures.
• Manage credentials and data source settings when moving from Desktop to Service.

Exercises

True / False

1. DirectQuery stores the full dataset in Power BI memory.
   Answer

   False — DirectQuery does not import the data; it queries the source at runtime.

2. SUMMARIZECOLUMNS can accept filters and measures.
   Answer

   True — it groups by columns and allows filters and expression columns.

3. DAX queries automatically respect the sort order defined in report visuals.
   Answer

   False — DAX queries require explicit ORDER BY to ensure ordering.

Fill in the Blanks

1. To create a calendar table in DAX use the ______ function.
   Answer

   CALENDAR

2. The clause used to set a starting point in an EVALUATE query is ______.
   Answer

   START AT

3. To avoid division by zero in measures use the ______ function.
   Answer

   DIVIDE()

Multiple Choice (MCQ)

1. Which storage mode is best for large datasets requiring near real-time access?
   • A. Import
   • B. DirectQuery
   • C. Composite
   Answer

   B. DirectQuery — it queries the source in real time without importing all data.

2. Which language is used for data transformation in Power Query?
   • A. DAX
   • B. M
   • C. SQL
   Answer

   B. M — Power Query Formula Language (M) handles transformations before loading.

Matching

Descriptive

1. Explain how you would implement incremental refresh for a transactional table.
   Answer

   Define RangeStart and RangeEnd parameters in Power Query, filter the table by those parameters, publish to Service, then configure the incremental refresh policy for the table (set store period and refresh period). Ensure the date column is a proper Date/DateTime type and test refresh on a dataset subset before full deployment.

2. Describe the difference between creating a date table in M vs DAX.
   Answer

   In M (Power Query) you generate and transform the date table before loading into the model, which can reduce model size and preprocessing cost. In DAX, a calculated table is created after the model is loaded and may be evaluated in the engine; DAX date tables are convenient for dynamic calculations but may affect refresh behavior and model size differently.

Creative

1. Design a small project: import sales data, build Dim_Calendar, compute monthly clothing orders and average profit per order, and publish to Power BI Service. Outline the steps.
   Answer

   1. Get Data from source (SQL/Excel), preview and transform in Power Query (filter, data types).
   2. Create parameters for incremental refresh if dataset large.
   3. Load data; in Model view add relationships between sales and product/date tables.
   4. Create Dim_Calendar via DAX: CALENDAR(MIN(...), MAX(...)) and add month/year columns.
   5. Create measures: [Orders], [Total Sales Profit]; compute Avg Profit per Order using DIVIDE.
   6. Use SUMMARIZECOLUMNS for verification and create report visuals; add ORDER BY in DAX queries used for exports.
   7. Publish to Power BI Service, configure dataset credentials and scheduled refresh, test visuals.

Activities, Vocabulary, Homework, Practice Task

Activities

• Walkthrough: connect to a sample SQL database and import tables.
• Hands-on: create Dim_Calendar and at least two measures; visualize monthly trend.

Vocabulary

• Data Source
• Calculated Table
• Measure
• Incremental Refresh
• DirectQuery

Homework

1. Build a sample report that filters sales to a single product category using SUMMARIZECOLUMNS.
2. Document steps for changing data source settings from development to production.

Practice Task

Write a DAX query that returns monthly orders for "Clothing" and sorts by month number.

EVALUATE
SUMMARIZECOLUMNS(
  'Date'[Month Name],
  'Date'[Month of Year],
  FILTER( VALUES('Product'[Category]), [Category] = "Clothing" ),
  "Orders", [Orders]
)
ORDER BY 'Date'[Month of Year] ASC