Launch Deal · 50% off 1st month ·

1.5 — Data Types, Column Transformations and Grouping

PL-300 Domain 1 · Skill 1.5

What You Need to Know

Choosing data types

The data type controls storage, sorting, which DAX functions work, and how visuals summarize the column. Set types deliberately rather than trusting the automatic Changed Type step.

Type Use for Watch out
Whole number Counts, integer keys Fails on decimals
Decimal number Measurements, ratios Floating point, so tiny rounding differences are possible
Fixed decimal number (currency) Money Exactly 4 decimal places, so sums don't drift
Percentage Rates Stored as a fixed decimal
Date, Time, Date/Time, Date/Time/Timezone, Duration Dates and times Split a Date/Time column into Date and Time when you don't need both: it shrinks the model and joins to a date table
Text Names, codes Codes with leading zeros (ZIP codes, product codes) must stay Text
True/False Flags
  • Options > Data Load > Type detection controls whether Power Query adds automatic type steps for unstructured sources.
  • Change types Using Locale when the source uses another regional format.

Creating and transforming columns

Feature What it does
Column From Examples Type the result you want for a few rows; Power Query writes the formula
Conditional Column If/else rules without writing M
Custom Column Any M expression, for example [Quantity] * [Unit Price]
Index Column A row number from 0 or 1, often used as a surrogate key
Split Column By delimiter, number of characters, positions, or case/digit transitions
Extract Length, first/last characters, range, text before or after a delimiter
Merge Columns Joins several columns into one text column with a separator
Date and Time tools Year, month, quarter, day of week, age and so on

The Transform tab changes a column in place. The Add Column tab keeps the original and adds a new one.

Group and aggregate rows

Home > Group By summarizes rows, like GROUP BY in SQL.

  • Basic: one grouping column and one aggregation.
  • Advanced: several grouping columns and several aggregations.
  • Operations: Sum, Average, Median, Min, Max, Count Rows, Count Distinct Rows, and All Rows, which keeps a nested table per group (useful for "latest row per customer" patterns).

Grouping in Power Query reduces the rows loaded into the model. Use it when the report never needs the detail rows (see lesson 2.8). If users must drill into detail, keep the rows and aggregate with DAX measures instead.

Key Concept: Get types right in Power Query, before the data reaches the model, and add new columns with the Add Column tab when you still need the original. Aggregate in Power Query only when the detail will never be needed.

Exam Traps

  • Storing ZIP codes or product codes with leading zeros as Whole number
  • Using Decimal number for currency when totals must match to the cent (use Fixed decimal number)
  • Grouping in Power Query when the report still has to show individual transactions
  • Keeping a full Date/Time column (with seconds) when only the date is used
  • Writing M by hand when Column From Examples or Conditional Column does the job

Check your understanding

Practice Scenario

A ProductCode column contains values such as 00451 and 01290. After loading, the report shows 451 and 1290, and lookups against another system fail. What should you do?

Report issue

Log in to save your progress.

Sources