Course: Data Transformation in Power BI
Here is a concise composition (13 sentences) in English about "Reshaping Data": Reshaping data is a foundational skill for analysts working in Power BI, enabling datasets to be transformed into structures suitable for analysis and visualization. In this chapter we emphasize DAX (Data Analysis Expressions) as a powerful complement to Power Query for creating calculated tables, performing aggregations, and running targeted queries. A common starting point is building a calendar with CALENDAR(MIN(...), MAX(...)) so that time intelligence measures operate correctly across the model. SUMMARIZECOLUMNS is shown as an efficient row-based summarization function to group by dimensions and attach measures such as Orders and Avg Profit per Order. When you need to pivot, unpivot, transpose, merge, or append, Power Query provides row/column transformations while DAX can create stable summarized tables inside the model. DAX queries (EVALUATE ... ORDER BY ...) are useful to extract, sort, and paginate results; remember to use ORDER BY and START AT when you want reproducible ordering or paging. Handling nulls is critical: use ISBLANK, COALESCE, or filters to avoid misleading aggregations and to present clear defaults like "Unknown". For joins, NATURALLEFTOUTERJOIN and other logical patterns let you implement left/right join semantics within DAX-calculated tables. Variables (VAR/RETURN) and custom functions help structure complex expressions, improving readability and performance. Ranking and statistical measures such as RANKX, STDEVX.P, and Z-score calculations allow you to surface outliers and relative performance. Comments and meaningful step names in both Power Query and DAX improve maintainability and team collaboration. Together, these techniques let you reshape raw facts into analytical shapes that tell a consistent, actionable story.Lesson
Topic: Reshaping Data — Power BI (DAX & Power Query)
Learning Outcomes
- Understand calculated tables and calendar creation in DAX.
- Summarize and aggregate data using SUMMARIZECOLUMNS and related functions.
- Use DAX queries with ORDER BY and START AT for controlled retrieval.
- Apply Power Query operations: pivot, unpivot, transpose, merge, append.
- Handle nulls, create conditional columns, and implement ranking and statistical measures.
Answer
By the end of this lesson students will be able to implement the examples shown (Dim_Calendar, SUMMARIZECOLUMNS queries), build robust ETL steps in Power Query, and write DAX measures for production reports.
Conceptual Explanation
Reshaping data means changing the table structure or aggregation level
to make data analytic-ready. In Power Query, reshape operations
(pivot/unpivot/transpose) change data layout before load. Inside the
model, DAX creates calculated tables and measures; common patterns
include a calendar via CALENDAR(MIN(...),MAX(...)), row
summaries with SUMMARIZECOLUMNS, and joins using
functions like NATURALLEFTOUTERJOIN.
| Snippet | Purpose |
|---|---|
Dim_Calendar = CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date])) |
Create continuous date table for time intelligence |
SUMMARIZECOLUMNS(..., "Orders", [Orders]) |
Group rows and attach measures (fast summarization) |
EVALUATE 'Sales Order' ORDER BY 'Sales Order'[Sales Order] ASC START AT "SO43661" |
Retrieve & paginate ordered query results |
COALESCE('Customer'[Name],"Unknown") |
Replace NULL/blank values with a default |
Answer
Use Power Query for structural reshaping pre-load; use DAX for model-level calculations, aggregations, ranking and analytics.
Bilingual (EN → বাংলা)
EN: Create a date table with CALENDAR(MIN(...), MAX(...)) to support time intelligence.
বাংলা: সময়-ভিত্তিক বিশ্লেষণের জন্য CALENDAR(MIN(...), MAX(...)) ব্যবহার করে একটি তারিখ টেবিল তৈরি করুন।
EN: Use SUMMARIZECOLUMNS to group by dimensions and compute row-level measures.
বাংলা: ডাইমেনশন অনুযায়ী গ্রুপ করার জন্য এবং সারি-ভিত্তিক পরিমাপ গণনা করার জন্য SUMMARIZECOLUMNS ব্যবহার করুন।
Answer
Keep both Power Query (M) and DAX roles clear: M for ETL, DAX for modeling/analytics.
Important Notes
- DAX queries ignore Power BI GUI sort — include ORDER BY explicitly.
- Use START AT to paginate or anchor query results.
- Prefer COALESCE/ISBLANK to manage nulls before aggregations.
- Rename steps and add comments in Power Query and DAX for maintainability.
- Measure calculations should avoid row-by-row context where aggregation is intended.
Answer
Follow naming and commenting conventions. Test expressions on a sample and check filter context; use VAR to simplify complex logic.
Exercises
True / False
- SUMMARIZECOLUMNS can include explicit measures in its argument list.
- DAX calc tables run in Power Query before load.
- ORDER BY in a DAX query uses the model's GUI sort automatically.
Answer
True — you can pass measure names or inline expressions to produce columns.
Answer
False — DAX calculated tables are created in the model after load. Power Query uses M.
Answer
False — you must specify ORDER BY explicitly in the DAX query.
Fill in the Blanks
- To create a calendar table in DAX you use __CALENDAR__ with MIN and MAX dates.
- To replace blank customer names use __COALESCE__('Customer'[Name], "Unknown").
- The DAX function to compute population standard deviation across rows is __STDEVX.P__.
Answer
CALENDAR
Answer
COALESCE
Answer
STDEVX.P
Multiple Choice Questions (MCQ)
- Which function best groups rows and computes multiple measures efficiently?
- A. FILTER
- B. SUMMARIZECOLUMNS
- C. LOOKUPVALUE
- D. VALUES
Answer
B. SUMMARIZECOLUMNS
- Which clause helps you start results at a specific key in a DAX query?
- A. WHERE
- B. START AT
- C. OFFSET
- D. BEGIN WITH
Answer
B. START AT
Matching
Match the item on the left with its purpose on the right.
| Item | Purpose |
|---|---|
| 1. UNPIVOT | A. Turn wide → long |
| 2. TRANSPOSE | B. Swap rows and columns |
| 3. NATURALLEFTOUTERJOIN | C. Left join semantics in DAX |
| 4. RANKX | D. Produce ordinal ranking by measure |
Answer
1→A, 2→B, 3→C, 4→D
Descriptive
- Explain when to use Power Query vs DAX when reshaping data.
- Describe a DAX pattern to compute average profit per order safely.
Answer
Use Power Query (M) for structural transformations (pivot, unpivot, merge, append) before loading data. Use DAX when you need model-level calculated tables, measures, ranking, or analyses that require the relational model and dynamic filter context.
Answer
Create measures: [Total Sales Profit] = SUM(...), [Orders] = COUNTROWS(...) then compute DIVIDE([Total Sales Profit],[Orders]) which avoids division-by-zero. Optionally wrap with COALESCE.
Creative
Design a short DAX query that summarizes monthly orders for "Clothing" and orders by month number.
Answer
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Month Name],
'Date'[Month of Year],
'Product'[Category],
FILTER( VALUES('Product'[Category]), 'Product'[Category] = "Clothing" ),
"Orders", [Orders]
)
ORDER BY 'Date'[Month of Year] ASC
Activities, Vocabulary, Homework, Practice Task
Activities
- Implement Dim_Calendar using your fact table and create Month/Quarter columns.
- Using Power Query, unpivot a quarterly-sales wide table into long form and reload.
- Create a calculated table that UNIONs two region sales tables and summarize by category.
Answer
Activity solutions: check calendar range and weekday/month columns; verify unpivoted row counts; ensure UNION uses SELECTCOLUMNS with identical column ordering.
Vocabulary
CALENDAR Create a contiguous date table in DAX. SUMMARIZECOLUMNS Group and produce aggregated rows with measures. UNPIVOT Transform wide→long in Power Query. RANKX Return ordinal ranking by a measure.Answer
Memorize these terms and test by producing short examples in your PBIX file.
Homework
- Create a PBIX sample: Dim_Calendar, Sales_Summary (by month & category), and one measure using RANKX.
- Document each step with comments and step renames in Power Query and DAX.
Answer
Submit PBIX or DAX scripts. Expect graders to check naming, comments, correct use of DIVIDE and COALESCE, and correct sorts.
Practice Task
Calculate Z-scores for product sales and flag items with Z > 1.5 (outliers).
Answer
Pattern: compute mean and STDEV.P for the sales set (measures or variables), then Z = (Sales - Mean) / StdDev; use FILTER to return items where Z > 1.5.
End of lesson — practice on a sandbox dataset. Save changes and document assumptions.