Prepare the data · Topic 5 of 8
Data Types and Column Transformations
PL-300 data types and columns: pick the right Power Query data type, change type with locale, and build columns with column from examples and conditional columns.
- 4 min
- Use Fixed decimal number for currency, Text for codes with leading zeros, Date when no time is needed.
- Change Type Using Locale fixes dates and numbers written in a different regional format.
- Transform changes a column in place. Add Column creates a new one.
What it is
Every column has a data type, and the type decides how Power BI stores, sorts, aggregates and formats the values. Choosing the right one is the first transformation on almost every table. After that you create and transform columns: split them, merge them, extract parts, and add new ones from rules or examples. Power Query records each action as an M step.
How it works
Choosing data types
| Type | Use for | Note |
|---|---|---|
| Whole number | Counts, IDs stored as numbers | 64 bit integer |
| Decimal number | General measurements | Floating point, may show tiny rounding differences |
| Fixed decimal number | Currency and money | Four decimal places, avoids rounding drift |
| Percentage | Rates | A decimal shown as a percent |
| Date | Calendar date only | Needed for a date table |
| Time | Time of day | |
| Date/Time | Date with time | |
| Date/Time/Timezone | Date, time and UTC offset | Useful for events across time zones |
| Duration | Elapsed time | |
| Text | Names, codes with leading zeros | |
| True/False | Flags |
Choose Text for identifiers such as postal codes and product codes. A number type would drop leading zeros. Choose Fixed decimal number for money, and Date instead of Date/Time when no time is needed, because it has fewer distinct values and compresses better.
Change type with locale
A date like 03/04/2025 means March 4 in the United States and April 3 in the United Kingdom. Right click the column, choose Change Type, then Using Locale, and pick the type and the locale of the source, not your own computer. The same applies to numbers where the decimal separator is a comma. Without locale, wrong values or errors appear.
Transforming columns
The Transform tab changes a column in place.
- Split Column by delimiter, number of characters or position.
- Format: Trim, Clean, case.
- Extract: first characters, last characters, text between delimiters, length.
- Replace Values, Fill, Pivot and Unpivot.
- Date and Time commands: year, month, name of month, day of week, age, earliest, latest.
- Merge Columns joins columns with a separator into one text column.
Number commands round, take absolute values or apply math. The Add Column tab uses the same actions, but they create a new column and leave the original untouched.
Adding columns
| Command | Use |
|---|---|
| Column From Examples | You type sample results, and Power Query infers the formula |
| Custom Column | You write an M expression |
| Conditional Column | If then else rules without code |
| Index Column | Adds a running number, starting at 0 or 1 |
| Duplicate Column | Copies a column |
| Invoke Custom Function | Applies a function to each row |
Column From Examples is useful for patterns. If you have "SMITH, JOHN" and type "John Smith" in the first row, Power Query proposes the transformation. A Conditional Column might assign a size band: Amount below 100 is Small, below 1,000 is Medium, otherwise Large.
Power Query or DAX
You can also create columns in DAX after loading. For shaping, prefer Power Query: the work is done once at refresh, it can fold back to the source in DirectQuery or Import, and it keeps the model smaller. Use DAX when the calculation needs model relationships or filter context.
A worked scenario
A UK retailer's file has OrderDate typed as text "13/04/2025" and a Price column with the "£" symbol. The analyst changes OrderDate to Date using locale English (United Kingdom), removes the symbol with Replace Values, and sets Price to Fixed decimal number. She then uses Column From Examples to split "SKU-0012-RED" into a Colour column, and a Conditional Column to label orders as Bulk or Standard.
What to remember for the exam
- Use Fixed decimal number for currency, Text for codes with leading zeros, Date when no time is needed.
- Change Type Using Locale fixes dates and numbers written in a different regional format.
- Transform changes a column in place. Add Column creates a new one.
- Column From Examples infers a formula from samples. Conditional Column builds rules without code.
- Prefer Power Query for shaping and DAX for calculations that need model context.
When a date column loads as text or with swapped day and month, think locale before anything else.
How the exam asks about this
- "Postal codes lose their leading zeros." Set the type to Text.
- "Dates in dd/MM/yyyy load wrongly." Change type using locale.
- "Create a column from a pattern without writing code." Column From Examples.
- "Label rows Small, Medium, Large by amount." A conditional column.
Common mistakes
Typing IDs as numbers. Sums and lost zeros follow.
Using Decimal for money. Rounding drift appears. Use Fixed decimal.
Ignoring the source locale. Dates swap day and month silently.
Adding calculated columns in DAX for simple shaping. It enlarges the model.
Check yourself
Which type suits currency values?
Fixed decimal number.
How do you load dates written as 13/04/2025 correctly on a US computer?
Change Type Using Locale with an English (United Kingdom) locale.
What is the difference between Transform and Add Column?
Transform changes the column in place, Add Column creates a new column.
Topic 5 of 8 in Prepare the data
Test yourself on Prepare the data
The full Microsoft PL-300 question bank has 657 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.