Course (Part 743)

Course: Data Transformation in Power BI

Lesson

Topic: Reshaping Data — DAX & Power Query techniques for transformation, aggregation, and querying in Power BI

Learning Outcomes

• Understand how to create calculated tables (e.g., calendar tables) using DAX.
• Use SUMMARIZECOLUMNS and related functions to group and aggregate data.
• Execute DAX queries to retrieve, sort, and paginate results from the model.
• Apply Power Query operations: pivot, unpivot, transpose, merge, and append.
• Handle nulls, use variables, and implement rankings and statistical measures.

Conceptual Explanation

Reshaping data is the process of converting data between wide and long forms, aggregating, and creating semantic structures that support analysis. In Power BI, transformations can be applied either in Power Query (M language) during data ingestion or in the model layer using DAX. Use Power Query to perform structural changes—pivot/unpivot, transpose, merge (joins), and append (union of similarly-structured tables). Use DAX for model-level constructs: calculated tables (CALENDAR), calculated columns, measures, grouping and aggregation (SUMMARIZE / SUMMARIZECOLUMNS), ranking (RANKX), and statistical calculations (STDEVX.P).

Key DAX examples:

-- Calendar table
Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey]))
-- Summarize by month and category
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])
)
-- Query with ordering and start point
EVALUATE
'Sales Order'
ORDER BY 'Sales Order'[Sales Order] ASC
START AT "SO43661"

Bilingual (EN → বাংলা)

English: Create calculated tables with CALENDAR for time intelligence; use SUMMARIZECOLUMNS to group and compute measures; perform shape changes in Power Query; control DAX query ordering explicitly.

বাংলা: সময়-নির্ভর বিশ্লেষণের জন্য CALENDAR দিয়ে ক্যালেন্ডার টেবিল তৈরি করুন; SUMMARIZECOLUMNS ব্যবহার করে গ্রুপিং ও মেজার গণনা করুন; ডেটার আকার পরিবর্তনের কাজ Power Query-তে করুন; DAX কুয়েরিতে অর্ডারিং স্পষ্টভাবে নির্ধারণ করুন।

Important Notes

• Power Query (M) is best for structural/shape transformations before data loads to the model.
• DAX is best for semantic calculations, measures, and calculated tables that use model relationships.
• DAX queries do not inherit the report sort order—use ORDER BY inside the query.
• Use COALESCE or ISBLANK to manage nulls; filters can exclude null categories from aggregates.
• Rename steps and add comments (// or /* */) to improve readability and collaboration.

Exercises

True / False

1. Calculated tables created with DAX are refreshed automatically when the model refreshes.
Answer

True. Calculated tables defined in DAX are recalculated during model refresh and will reflect updated source data or related measures.

2. Power Query transformations are applied after DAX calculations in the data pipeline.
Answer

False. Power Query transformations (M) are applied before data loads into the model; DAX calculations operate on the model after loading.

3. SUMMARIZECOLUMNS can group by multiple columns and compute measures per group.
Answer

True. SUMMARIZECOLUMNS accepts multiple group-by columns, optional filters, and returns computed expressions per row.

Fill in the Blanks

1. To create a continuous date table in DAX you can use the ________ function.
Answer

CALENDAR

2. To avoid division-by-zero when computing ratios in DAX, use the ________ function.
Answer

DIVIDE

3. In Power Query, the operation that converts multiple period columns into rows is called ________.
Answer

Unpivot (often "Unpivot Columns")

Multiple Choice Questions (MCQ)

1. Which function ranks items by a measure in DAX?
   1. RANKX
   2. RANK
   3. POSITION
   4. ORDERBY
   Answer

   a) RANKX

2. Which clause controls starting row in a DAX query?
   1. OFFSET
   2. START AT
   3. BEGIN FROM
   4. FIRST ROW
   Answer

   b) START AT

Matching

Descriptive

1. Explain when you would use Power Query versus DAX for reshaping data.
Answer

Use Power Query (M) when you need to change row/column structure, merge or append sources, or clean and normalize data before it enters the model. Use DAX when you need model-aware calculations, measures, ranking, or calculated tables that rely on relationships and measures created in the model.

2. Describe a step-by-step approach to create a summarized table of monthly orders for one product category.
Answer
1. Ensure a Date dimension exists (create with CALENDAR if needed).
2. Confirm relationships: fact table → Date, Product dimension linked appropriately.
3. Define measures: [Orders], [Total Sales Profit].
4. Use SUMMARIZECOLUMNS grouping by 'Date'[Month Name], 'Date'[Month of Year], 'Product'[Category].
5. Apply FILTER( VALUES('Product'[Category]), 'Product'[Category] = "Clothing" ) to restrict the category.
6. Compute additional expressions like DIVIDE([Total Sales Profit],[Orders]) for averages and include ORDER BY 'Date'[Month of Year] ASC for chronological output.

Creative

1. Design a small analysis plan (3 steps) to find top 5 products by Z-score of monthly sales.
Answer
1. Create a monthly sales summary table (group by Product, Month) using SUMMARIZE or in Power Query aggregate to monthly granularity.
2. Compute mean and standard deviation per product across months and calculate Z-score: Z = (Sales - Mean) / StdDev (use STDEVX.P or STDEV.P and variables in DAX).
3. Rank by Z-score (RANKX) and filter/topN to extract top 5 product-month combinations; visualize with a bar or table and annotate outliers.

Activities

• Hands-on: Create Dim_Calendar using CALENDAR for your dataset and relate it to the fact table.
• Exercise: Use Power Query to unpivot quarterly sales and then build a SUMMARIZECOLUMNS query to aggregate monthly sales.
• Lab: Implement COALESCE to replace null customer names with "Unknown" and show the difference in aggregate counts.

Vocabulary

• CALENDAR — DAX function to generate date ranges.
• SUMMARIZECOLUMNS — DAX function to group and return tabular summaries with expressions.
• UNPIVOT / PIVOT — Power Query operations to change data orientation.
• START AT — DAX query clause to control result pagination.
• COALESCE / ISBLANK — Functions to handle nulls and blanks.
• RANKX, STDEVX.P — DAX functions for ranking and standard deviation calculations.

Homework

1. Create a calculated table Dim_Calendar for your dataset and add Month, Quarter, Year columns.
Answer

Use: Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey])) and then ADDCOLUMNS to generate MonthName, MonthOfYear, Quarter, Year.

2. Build a DAX query that summarizes Orders and Avg Profit per Order for the "Clothing" category and sorts by Month.
Answer

EVALUATE
SUMMARIZECOLUMNS(
  'Date'[Month Name],
  'Date'[Month of Year],
  FILTER( VALUES('Product'[Category]), 'Product'[Category] = "Clothing" ),
  "Orders", [Orders],
  "Avg Profit per Order", DIVIDE([Total Sales Profit], [Orders])
)
ORDER BY 'Date'[Month of Year] ASC

Practice Task

Given separate tables Sales_2021 and Sales_2022 with identical columns, create a unified table and a summary that shows total sales per product category.

Unified_Sales =
UNION(
  SELECTCOLUMNS(Sales_2021, "OrderID", Sales_2021[OrderID], "ProductID", Sales_2021[ProductID], "Total Sales", Sales_2021[TotalSales]),
  SELECTCOLUMNS(Sales_2022, "OrderID", Sales_2022[OrderID], "ProductID", Sales_2022[ProductID], "Total Sales", Sales_2022[TotalSales])
)
EVALUATE
SUMMARIZECOLUMNS(
  'Product'[Category],
  "Total Sales", SUM(Unified_Sales[Total Sales])
)

End of lecture sheet — practice combining Power Query and DAX techniques for efficient, maintainable data reshaping in Power BI.