This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
--> Transform and load the data
--> Create and Transform Columns
Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.
Columns are the foundation of data analysis in Power BI. The ability to create new columns and transform existing ones is essential for shaping your dataset into a structure that supports meaningful insights and accurate reports.
In the PL-300 exam, Microsoft tests not only whether you can perform transformations but also whether you understand when and why to apply them.
Why Create and Transform Columns?
Before data can be modeled and visualized:
- It must be clean, consistent, and in the right format
- New columns may be needed to support business logic
- Transformations ensure data is reliable and analysis-ready
For example:
- Converting text dates into true Date types
- Extracting parts of a string (e.g., Year from a date)
- Splitting a full name into first and last names
- Normalizing inconsistent text values
These are not just useful—they are often necessary for accurate DAX measures and reporting.
Where Column Transformations Happen
Most column creation and transformation tasks happen in Power Query Editor (before the data loads into the model).
Key places include:
- Transform tab
- Add Column tab
- Applied Steps pane
- Advanced Editor (for M code)
Power BI also allows column creation after loading the data through:
- DAX Calculated Columns (in the data model)
The exam may present scenarios where you choose which tool (Power Query vs DAX) to use.
Common Column Transformations
Here are the main categories of column operations you should be ready to apply:
1. Basic Transformations
These change existing columns:
- Rename columns
- Change data types
- Trim, clean, or format text
- Replace values
- Remove columns
These are the bread-and-butter tasks that clean and standardize data.
2. Splitting and Merging Columns
When data is combined within one field:
- Split Column (by delimiter or number of characters)
- Example: Split
Full Name→First NameandLast Name
- Example: Split
- Merge Columns
- Example: Combine
CityandStateinto a single location field
- Example: Combine
This is essential when data needs to be restructured for modeling.
3. Extracting Components
Examples include:
- Extracting Year, Month, or Day from a Date column
- Taking the first/last characters from a text string
- Extracting text before or after a specific character
These operations prepare granular fields needed for grouping or calculations.
4. Calculations Using “Add Column”
You can create derived columns based on logic:
- Custom Columns (via M formulas)
- Conditional Columns
- Example: Flag
High Valuesales where sales > $1,000
- Example: Flag
- Index Columns
- Useful for row ordering
These columns often support business metrics or classifications.
Text Transformations
Text columns commonly require cleaning and standardization:
- Uppercase / Lowercase
- Trim (removes leading/trailing spaces)
- Clean (removes non-printable characters)
- Replace Values (e.g., “N/A” → null)
The exam often tests whether you know how to fix inconsistent text data.
Date and Time Transformations
Working with dates is core to analysis:
- Change text to date/time type
- Extract Year, Quarter, Month, Day
- Add custom time intelligence columns
- Use locale conversion for date parsing
This enables time-based grouping and accurate measures like YTD (Year-to-Date).
Conditional and Custom Columns
Conditional Columns
- Created through UI (Add Column → Conditional Column)
- Define logic visually (e.g.,
if Sales > 500 then "High" else "Low")
Custom Columns
- Created using Power Query M code
- More advanced logic and functions
Both are useful depending on the complexity of your requirement. Exam questions often compare these approaches.
Column Transformations vs DAX Calculated Columns
Power Query Column
- Transformation occurs before data loads into model
- Changes physical data shape
- Useful for cleaning and structuring data
DAX Calculated Column
- Created after data loads into the model
- Evaluated per row in the model
- Useful for measures and relationships tied to data model context
Exam insight:
Use Power Query transformations for structural cleanup. Use DAX calculated columns when the logic depends on model relationships or evaluation context.
Best Practices for the Exam
- Clean data before creating columns.
Don’t derive new data from dirty input. - Apply the right transformation tool.
Power Query for structural cleanup; DAX for model-aware calculations. - Name columns clearly.
Report consumers and measures depend on intuitive names. - Avoid unnecessary columns.
Only keep what’s needed for reporting to improve model performance. - Group related transformations logically.
Use Query folding where possible (especially for large datasets).
How This Appears on the PL-300 Exam
You might see scenarios like:
You need to split a full address column into street, city, and postal code for better filtering. Which transformation should you use?
This tests:
- Knowledge of Split Column
- When to apply it
- How to maintain data type integrity afterward
Or:
Your date column is text and not aggregating correctly. What do you do?
This tests:
- Understanding of data types
- Ability to convert to proper Date/Time
Most questions are scenario-based, requiring both decision and action reasoning.
Key Takeaways
✔ Column transformations are a core part of shaping data
✔ Power Query is the primary environment for creating and transforming columns
✔ Use Add Column for new fields and Transform for modifying existing fields
✔ Know the difference between Power Query and DAX calculated columns
✔ Common transformations include text, date, splitting/merging, conditional logic, and custom formulas
Practice Questions
Go to the Practice Exam Questions for this topic.
The Data Community 