Reshaping and Business-Ready Structures

Introduction

Operational systems rarely store data in the form required for analysis. A sales system may record months as columns, a survey may store repeated responses across fields, and a warehouse export may contain several rows for one business transaction. Power BI therefore requires more than importing data: it requires transforming data into a consistent, interpretable and analysis-ready structure.

This chapter examines reshaping operations in Power Query: pivot, unpivot, transpose, grouping, aggregation, filling, indexing and nested-table operations. These transformations are not merely interface actions. They express assumptions about the meaning, grain and relationships of data. A technically successful query can still produce misleading results if it changes the grain of a table, duplicates measures or treats missing values incorrectly.

The central design principle is tidy, business-ready data. In a typical analytical table, each row represents one clearly defined observation, each column represents one attribute or measure, and each cell contains one value. Such structures support reliable dimensional models, reusable measures, filtering, drill-through and auditability.

Learning Objectives

By the end of this chapter, you should be able to:

1. Explain why operational layouts must often be reshaped before analysis.
2. Select between pivot, unpivot and transpose operations.
3. Group records and calculate appropriate aggregations without changing business meaning.
4. Use fill and index operations responsibly.
5. Work with nested tables created by grouping and other transformations.
6. Identify grain, keys, missing values and duplication risks.
7. Design a Power Query workflow that produces dimensional-model-ready data.
8. Evaluate the governance and performance implications of reshaping decisions.

1. Data Shape, Grain and Tidy Structure

1.1 Operational structure versus analytical structure

Operational databases are designed to support transactions, data entry and application processes. They may contain normalized tables, coded fields, repeated columns and report-oriented layouts. Analytical models have different priorities: consistent filtering, aggregation, comparison over time and understandable relationships.

A table’s grain is the business meaning of one row. Examples include:

• one row per sales-order line;
• one row per customer per month;
• one row per product and warehouse;
• one row per employee absence event.

Every transformation should preserve or deliberately redefine this grain. If a sales-line table is grouped by customer, it is no longer a sales-line table; it becomes a customer summary table. Confusing these two grains is a major source of incorrect totals.

1.2 Tidy data

A tidy table generally follows three principles:

Tidy structure does not mean that every table must have one row per individual event. A monthly customer summary can be tidy if its grain is explicitly defined and consistently maintained.

1.3 Keys and uniqueness

A key is a field, or combination of fields, that identifies a row at its stated grain. For an order-line table, OrderID alone may not be unique; the key may be OrderID plus LineNumber. Before pivoting, grouping or joining, determine which columns identify an observation. A transformation that assumes uniqueness when it does not exist can create errors or silently combine values.

2. Unpivoting: From Wide Reports to Long Analytical Tables

Unpivoting converts several columns into attribute-value pairs. It is especially useful when periods, categories or measures have been stored as column headings.

Consider this operational report:

The columns Jan, Feb and Mar represent values of one variable: Month. A tidy version is:

In Power Query, select the identifier columns Product and Region, then choose Unpivot Other Columns. The resulting Attribute column contains the former headers and the Value column contains their values. Rename these columns to business terms such as Month and Sales.

A representative M expression is:

Table.UnpivotOtherColumns(Source, {"Product", "Region"}, "Month", "Sales")

The phrase Other Columns is important. It makes the query more resilient when new month columns are added, provided that the identifier columns remain stable. By contrast, selecting specific columns to unpivot can be safer when the source includes unrelated fields that must not be transformed.

2.1 When unpivoting is appropriate

Unpivot when columns represent repeated instances of the same variable, such as months, survey questions, channels or scenarios. Do not unpivot genuinely different variables merely because they are adjacent. For example, Quantity, UnitPrice and Discount are different measures and usually should remain separate columns in a sales-line table.

2.2 Common unpivot risks

Unpivoting can produce unexpected rows when:

• blank columns are included;
• totals columns such as AnnualTotal are treated as ordinary periods;
• headers contain inconsistent spelling;
• multiple header rows have not been promoted or combined correctly;
• a report contains subtotals that are mistaken for records.

Inspect the resulting row count and compare it with the expected number of identifier combinations and periods.

3. Pivoting: Converting Attribute Values into Columns

Pivoting is the reverse conceptual operation. It takes values in one column and creates columns from them. Suppose a table contains Product, Month and Sales. Pivoting Month creates Jan, Feb and Mar columns.

Pivoting is useful for presentation, matrix-style exports and some intermediate calculations. However, a dimensional model usually benefits from the long form because dates, categories and measures remain easier to filter and extend.

In Power Query, select the attribute column, choose Pivot Column, select the value column and specify an aggregation if more than one value exists for a key combination. A representative expression is:

Table.Pivot(Source, List.Distinct(Source[Month]), "Month", "Sales", List.Sum)

The aggregation function is not a technical detail; it is a business decision. If two rows exist for Product A and Jan, List.Sum assumes that the values should be added. That may be correct for sales transactions but incorrect for account balances, percentages or already aggregated figures.

3.1 Pivot and duplicate combinations

Suppose the source contains:

A pivot requires one value for A-Jan. Using Sum returns 120. Using Max returns 70. Using a custom list may return both values but creates a structurally different result. The correct choice depends on the measure’s additive behavior and the source grain.

3.2 Additivity and aggregation

Measures can be classified as:

• Additive: can be summed across the relevant dimensions, such as units sold.
• Semi-additive: can be summed across some dimensions but not others, such as inventory balance, which may be summed across products but not across dates.
• Non-additive: should not normally be summed, such as ratios, percentages and unit prices.

Power Query aggregation should respect this classification. Averages also require care: averaging daily averages without weighting can produce a different result from calculating an average from the underlying totals and counts.

4. Transpose: Changing Rows into Columns

Transpose exchanges rows and columns. A small parameter table such as:

may be transposed into a horizontal layout. Transpose can also help repair poorly structured files where labels are arranged vertically.

Transpose should not be confused with unpivoting. Unpivoting preserves identifier columns and converts repeated variable columns into rows. Transpose changes the physical orientation of the whole table and can make data types, headers and row identity difficult to interpret.

A common workflow is:

1. Remove title, note and blank rows.
2. Transpose the remaining table.
3. Promote the first row as headers.
4. Rename columns and assign data types.
5. Validate uniqueness and missing values.

Use transpose sparingly in production queries. It is sensitive to source layout changes and can be difficult for another developer to understand without documentation.

5. Grouping and Aggregation

Grouping partitions rows according to one or more fields. It can produce a summary table or a nested table containing the original rows for each group.

For example, grouping sales lines by CustomerID and calculating total sales produces one row per customer. In the Power Query interface, choose Group By, select the grouping columns, name the new column and choose an operation such as Sum, Count Rows, Average or All Rows.

Example M expression:

Table.Group(
    Sales,
    {"CustomerID"},
    {{"TotalSales", each List.Sum([SalesAmount]), type number},
     {"OrderLines", each Table.RowCount(_), Int64.Type}}
)

Here, each represents the current group and underscore represents the table containing that group. The expression creates two aggregations: total sales and number of rows.

5.1 Grouping as a change of grain

If the input grain is one row per order line and the grouping field is CustomerID, the output grain is one row per customer. Do not append customer-level totals back to the original table unless the duplication is intentional and understood. A customer total repeated on every order line will be counted incorrectly if summed in a report.

5.2 Useful aggregation patterns

To count distinct orders in order-line data, count distinct OrderID values rather than rows. A row count measures line items, not orders.

6. Nested Tables and Hierarchical Transformations

A nested table is a table stored inside a cell of another table. Group By with the All Rows operation creates this structure. The outer table contains one row per group, while a nested column contains the detailed records for that group.

For example, grouping by CustomerID with All Rows creates:

Nested tables are useful for group-specific operations that cannot be expressed as a simple aggregation. Examples include:

• selecting the latest transaction for each customer;
• calculating a ranking within each product category;
• retaining the top three orders per customer;
• applying a custom function to each group;
• identifying the first and last event.

A common pattern is:

Grouped = Table.Group(
    Sales,
    {"CustomerID"},
    {{"Rows", each _, type table}}
),
SortedGroups = Table.TransformColumns(
    Grouped,
    {"Rows", each Table.Sort(_, {{"OrderDate", Order.Descending}})}
),
Latest = Table.AddColumn(
    SortedGroups,
    "LatestOrder",
    each Table.FirstN([Rows], 1)
)

The operation must sort each nested table before selecting the first row. Without sorting, the first row is not necessarily the latest transaction. After extracting required attributes, remove the nested table column if it is no longer needed. Keeping large nested tables can increase memory use and make the model harder to inspect.

Nested-table workflows should also address empty groups, null dates and ties. If two transactions share the same maximum date, define a secondary ordering field, such as OrderID, to make the result deterministic.

7. Fill Operations and Missing Context

Operational exports often use a value once and leave following rows blank. For example:

If the blanks mean that the North label applies to the following rows, Fill Down converts them to North. In Power Query, select the column and choose Fill Down. The equivalent M expression is:

Table.FillDown(Source, {"Region"})

Fill Up performs the reverse direction. It may be useful when a label appears after the records to which it applies.

7.1 Fill is not imputation

Fill operations propagate existing values; they do not discover the correct value. Fill Down is valid only when the source layout establishes that blank cells inherit the preceding nonblank value. It is not valid for missing sales amounts, unknown customer segments or absent measurements. Those cases require a business rule, such as replacement with zero, retention as null, lookup from a reference table or exclusion from a calculation.

Before filling:

1. Sort rows in the correct logical order.
2. Confirm that blank values represent continuation rather than missing data.
3. Remove report titles, subtotals and page breaks.
4. Validate the result against known records.

8. Index Columns and Row Identity

An index column assigns a sequential integer to rows. It can begin at zero or one, and it can use a specified increment. Indexes are useful for:

• preserving source order after sorting;
• identifying rows in an audit extract;
• creating a temporary positional reference;
• generating a surrogate key in a controlled staging process;
• comparing a row with the preceding or following row.

An index is not automatically a business key. If the source is refreshed in a different order, index values can change. For a durable dimension key, use a stable key-generation strategy and document its behavior. When adding an index after sorting, the index represents that sorted order; adding it before sorting gives a different meaning.

For example, to calculate a change from one row to the next, an index can support a self-join or list-based lookup. Such procedures require explicit ordering and careful treatment of the first row, for which no prior value exists.

9. A Professional Reshaping Workflow

A robust workflow separates structural preparation from business calculations.

Stage 1: Profile the source

Inspect column names, data types, null counts, distinct values, duplicate keys and unexpected rows. Determine whether the file contains multiple header rows, subtotals or notes.

Stage 2: Establish the grain

Write a plain-language statement such as: one row represents one product sold on one order line. Identify the candidate key and test whether it is unique.

Stage 3: Clean structural defects

Remove irrelevant rows, promote headers, trim text, standardize column names and correct data types. Do this before grouping or pivoting so that nulls and text variants do not create separate groups.

Stage 4: Reshape

Choose unpivot for repeated columns, pivot for controlled column creation, transpose for orientation problems, fill for inherited labels and group for a new summary grain.

Stage 5: Validate

Compare row counts, totals, distinct keys and sample records before and after transformation. Reconcile known totals with the source system.

Stage 6: Load according to model role

A detailed fact table should normally retain its transaction grain. Summary tables may be useful for performance or special reporting, but they should not replace detail when users need drill-down.

10. Detailed Applied Case: Regional Retail Sales

Background

This illustrative case concerns Northstar Retail, which receives monthly spreadsheets from regional managers. Each spreadsheet contains a title, merged headings, one row per product, and separate columns for sales in April, May and June. Regional labels appear only on the first row of each section. Some files also contain a quarterly total column.

Problem

The business wants a Power BI model with a Sales fact table, Product and Region dimensions, and a Date dimension. The initial files are unsuitable because:

• months are columns rather than values;
• region labels are blank on continuation rows;
• quarterly totals would duplicate monthly sales if imported as a month;
• product codes contain inconsistent spaces;
• one product appears twice in a region because the source uses separate order categories.

Analysis

The intended grain is one row per product, region, month and order category. Product plus region is therefore not a unique key in the source. The quarterly total is a derived value and should not be loaded as an additional period.

Approach and solution

1. Remove title rows and blank separators.
2. Promote the correct header row.
3. Fill Region down after confirming that blanks indicate continuation.
4. Trim ProductCode and standardize its type as text.
5. Remove QuarterlyTotal before unpivoting.
6. Unpivot April, May and June into Month and SalesAmount.
7. Convert Month to a proper date using a reference mapping or a constructed date based on the reporting year.
8. Group only if the business requires one row per product-region-month. In that case, sum SalesAmount across order categories and retain the category only if it remains analytically relevant.
9. Validate that total sales across all transformed rows equal the sum of monthly source values, excluding the quarterly total.
10. Load the resulting fact table and relate it to dimensions using stable keys.

A simplified transformation sequence might include:

Cleaned = Table.TransformColumns(Source, {{"ProductCode", Text.Trim, type text}}),
Filled = Table.FillDown(Cleaned, {"Region"}),
WithoutQuarter = Table.RemoveColumns(Filled, {"QuarterlyTotal"}),
Long = Table.Unpivot(
    WithoutQuarter,
    {"April", "May", "June"},
    "Month",
    "SalesAmount"
),
Grouped = Table.Group(
    Long,
    {"Region", "ProductCode", "Month"},
    {{"SalesAmount", each List.Sum([SalesAmount]), type number}}
)

Outcome

The final table has a stable and explicit grain. Monthly trends can be displayed without double counting, new products can be added without adding model columns, and the quarterly total is calculated by the model or report rather than imported as a competing measure.

Learning points

The most important decision was not which button to click. It was identifying the grain and distinguishing source presentation fields from business data. The case also demonstrates that validation must include reconciliation, not only a successful refresh.

11. Performance, Governance and Quality Considerations

11.1 Query folding

When the source supports query folding, filters, column selection and some grouping operations may be translated to the source system. This can reduce data transferred to Power Query. Complex custom functions, some nested-table transformations and operations on files may prevent folding. Apply source-reducing steps early, but verify folding rather than assuming it.

11.2 Refresh stability

Avoid relying on column positions, uncontrolled automatic type detection and accidental worksheet ranges. Prefer named tables, explicit column lists where appropriate, stable headers and documented assumptions. If a new month arrives, an unpivot-other-columns strategy may be appropriate; if a new administrative column arrives, an explicit selection may be safer.

11.3 Data quality controls

Useful controls include:

• row-count checks before and after reshaping;
• duplicate-key detection;
• null checks for required fields;
• reconciliation of additive measures;
• allowed-value checks for dimensions;
• refresh logging and error queries.

11.4 Governance and ethics

Reshaping can expose personal information, combine records across systems or create derived classifications. Apply data minimization, access controls and retention policies. Do not use Fill Down or aggregation to conceal uncertainty. Keep an audit trail of source files, transformation logic and business assumptions. If an index is used as a key, document whether it is stable across refreshes.

11.5 AI-assisted transformation

AI assistants can suggest M code, identify likely headers or explain errors. They do not know the organization’s grain, accounting rules or privacy obligations unless these are explicitly supplied. Review generated code, test it against controlled examples and inspect whether it introduces implicit aggregation or removes important fields.

12. Best-Practice Checklist

Before publishing a reshaped query, ask:

• Is the grain stated in one sentence?
• Are the key columns identified and tested?
• Are repeated variables represented as rows where appropriate?
• Have totals and subtotals been excluded from transactional data?
• Is every aggregation supported by a business rule?
• Are missing values distinguished from inherited labels?
• Is row order meaningful before using Fill or Index?
• Are nested tables sorted before selecting first or last records?
• Have data types been assigned explicitly?
• Have totals, row counts and distinct keys been reconciled?
• Is the query understandable and maintainable by another developer?

Chapter Summary

Reshaping converts operational layouts into structures that support reliable analysis. Unpivoting is generally appropriate when repeated variables are stored as columns; pivoting creates columns from attribute values and requires a defensible aggregation rule. Transpose changes table orientation and is most useful for repairing small, poorly structured inputs.

Grouping changes the grain of a table and must be treated as a business decision. Aggregations must respect whether measures are additive, semi-additive or non-additive. Nested tables allow group-specific operations such as selecting the latest record, but they require explicit sorting and careful handling of ties. Fill operations propagate labels only when the source layout proves that inheritance is intended. Index columns provide positional identity, not necessarily durable business keys.

A professional Power Query solution begins with profiling and grain definition, applies structural cleaning before reshaping, validates totals and keys, and documents assumptions. These practices produce dependable fact and dimension structures and reduce the risk of silent analytical error.

Key Terms

Grain: The business meaning represented by one row in a table.

Tidy data: Data arranged so that each observation is a row, each variable is a column and each cell contains one value.

Key: A field or combination of fields that identifies a row at a defined grain.

Unpivot: A transformation that converts columns into attribute-value rows.

Pivot: A transformation that converts attribute values into columns.

Transpose: A transformation that exchanges rows and columns.

Grouping: Partitioning rows according to one or more fields.

Aggregation: Combining multiple values using an operation such as sum, count, minimum or average.

Additive measure: A measure that can be summed across the relevant dimensions.

Semi-additive measure: A measure that can be aggregated across some dimensions but not others.

Non-additive measure: A measure, such as a ratio, that should not normally be summed.

Nested table: A table stored within a cell of another table.

Fill Down: Propagating the last nonblank value into following rows.

Fill Up: Propagating a subsequent nonblank value into preceding rows.

Index column: A sequential positional column added to a table.

Query folding: Translation of Power Query operations into operations performed by the source system.

Fact table: A table containing business events or measurements at a defined grain.

Dimension: A table containing descriptive attributes used to filter and group facts.

Surrogate key: A system-generated identifier used to represent an entity in a model.

Reconciliation: Comparing transformed results with trusted source totals or records.

Review Questions

Short Answer Questions

1. What is meant by the grain of a table?
2. When is unpivoting preferable to pivoting?
3. Why can a row count be different from an order count?
4. What assumption must be true before using Fill Down?
5. Why is an index column not automatically a durable business key?

Long Answer Questions

1. Explain the differences between pivot, unpivot and transpose, including one suitable use case for each.
2. Discuss how grouping changes table grain and why aggregation rules must reflect measure additivity.
3. Explain how nested tables can be used to find the latest transaction for each customer.
4. Describe a professional workflow for converting a spreadsheet report into a dimensional-model-ready fact table.
5. Evaluate the main performance, governance and data-quality risks associated with reshaping in Power Query.

Analytical / Application Questions

1. A sales file contains Product, Region, Jan, Feb, Mar and AnnualTotal. Design a transformation that produces a monthly fact table without double counting. State the expected grain and validation checks.
2. A customer table contains multiple transactions and a blank customer segment on continuation rows. Explain how you would determine whether Fill Down is valid and how you would test the result.
3. A grouped table contains one nested transaction table per customer. Design a method to select the latest transaction when two transactions have the same date, and explain why your tie-breaking rule is necessary.

Multiple Choice Questions with Answers

Question 1

Which transformation is most appropriate when January, February and March are stored as separate columns representing one variable?

A. Unpivot

B. Transpose

C. Fill Up

D. Group By

Correct Answer: A. Unpivot

Question 2

What does grouping sales-line data by CustomerID normally do to the table grain?

A. It changes the grain to one row per customer

B. It creates one row per product and month

C. It preserves one row per sales line in all cases

D. It converts every numeric field into text

Correct Answer: A. It changes the grain to one row per customer

Question 3

Which aggregation is usually appropriate for additive sales amounts when duplicate Product-Month combinations are pivoted?

A. List.Sum

B. List.Max

C. List.First

D. List.Min

Correct Answer: A. List.Sum

Question 4

Fill Down is valid when blank values indicate that:

A. The preceding nonblank label applies to subsequent rows

B. The missing measure should be replaced with zero

C. All missing values are random

D. The next row contains the correct value

Correct Answer: A. The preceding nonblank label applies to subsequent rows

Question 5

Why should a nested table be sorted before selecting its first row as the latest transaction?

A. The first row is not necessarily the latest without an explicit order

B. Sorting changes all text values to numbers

C. Nested tables cannot contain dates unless sorted

D. Sorting automatically removes duplicate customers

Correct Answer: A. The first row is not necessarily the latest without an explicit order

Question 6

Which measure is generally non-additive?

A. Units sold

B. Revenue

C. Unit price

D. Number of order lines

Correct Answer: C. Unit price

Question 7

What is a principal risk of importing an AnnualTotal column together with monthly values and then summing them?

A. The same sales may be counted twice

B. The data will automatically become a dimension

C. All dates will become invalid

D. The source will no longer support query folding

Correct Answer: A. The same sales may be counted twice

Question 8

What is the primary role of an index column?

A. To provide sequential positional identity

B. To guarantee a permanent business key

C. To calculate a weighted average automatically

D. To replace all dimension keys

Correct Answer: A. To provide sequential positional identity

Question 9

Which operation creates columns from values in an attribute column?

A. Pivot

B. Unpivot

C. Fill Down

D. Transpose only

Correct Answer: A. Pivot

Question 10

If one row represents one order line, which operation is needed to count distinct orders correctly?

A. Count all rows only

B. Count distinct OrderID values

C. Sum UnitPrice

D. Average OrderID

Correct Answer: B. Count distinct OrderID values