Creating and transforming columns in Power BI is a fundamental skill for data preparation that allows analysts to shape data according to their reporting needs. This process primarily occurs in Power Query Editor, where you can manipulate data before loading it into your model.
**Creating New Colu…Creating and transforming columns in Power BI is a fundamental skill for data preparation that allows analysts to shape data according to their reporting needs. This process primarily occurs in Power Query Editor, where you can manipulate data before loading it into your model.
**Creating New Columns:**
You can add custom columns using several methods. The 'Add Column' tab provides options like 'Custom Column' where you write M code expressions, 'Column from Examples' where Power BI learns patterns from your input, and 'Conditional Column' for creating columns based on if-then-else logic. Index columns can be added to provide sequential numbering, while duplicate columns create copies of existing ones.
**Transforming Existing Columns:**
The 'Transform' tab offers extensive options for modifying columns. Text transformations include changing case (uppercase, lowercase, capitalize), trimming whitespace, extracting substrings, and splitting columns by delimiter or character count. Number transformations allow rounding, calculating statistics, and performing mathematical operations.
Date and time columns can be transformed to extract year, month, day, quarter, week number, or calculate age and duration. You can also parse text into dates or combine date and time columns.
**Data Type Conversions:**
Changing data types ensures proper analysis. You can convert text to numbers, dates, or boolean values. This step is crucial for calculations and relationships to function correctly.
**Merging and Splitting:**
Multiple columns can be merged into one using a specified delimiter. Conversely, single columns containing combined data can be split into multiple columns based on delimiters, character positions, or transitions between character types.
**Replace Values:**
The replace values function allows you to substitute specific values within columns, which is useful for standardizing data or correcting errors.
All transformations are recorded as steps in the Applied Steps pane, creating a repeatable transformation sequence that executes each time data refreshes.
Create and Transform Columns in Power BI - Complete Guide
Why Is This Important?
Creating and transforming columns is a fundamental skill for Power BI Data Analysts. Raw data rarely comes in the exact format needed for analysis. You must reshape, calculate, and derive new information from existing columns to build meaningful reports. This topic accounts for a significant portion of the PL-300 exam's 'Prepare the Data' section.
What Are Column Transformations?
Column transformations involve modifying existing columns or creating new ones in Power Query Editor or using DAX. These operations include:
• Adding custom columns - Creating new columns using formulas • Splitting columns - Dividing one column into multiple columns • Merging columns - Combining multiple columns into one • Changing data types - Converting text to numbers, dates, etc. • Replacing values - Substituting specific values with others • Extracting information - Pulling parts of text or dates
How It Works in Power Query
Adding Custom Columns: Navigate to Add Column tab → Custom Column. Write M code formulas to calculate values based on other columns.
Splitting Columns: Select the column → Transform tab → Split Column. Choose by delimiter, number of characters, or positions.
Merging Columns: Select multiple columns → Transform tab → Merge Columns. Specify a separator between values.
Conditional Columns: Add Column tab → Conditional Column. Create if-then-else logic to assign values based on conditions.
Column from Examples: Add Column tab → Column from Examples. Power BI infers the transformation pattern from examples you provide.
How It Works with DAX
Calculated columns use DAX formulas and are computed row by row:
• Created in the Data view or model • Stored in the data model and consume memory • Useful when you need row context for calculations • Example: Profit = [Revenue] - [Cost]
Key Differences: Power Query vs DAX Columns
Power Query columns are processed during data refresh and are preferred for static transformations. DAX calculated columns are evaluated after data load and are better for calculations requiring model relationships.
Exam Tips: Answering Questions on Create and Transform Columns
1. Know when to use Power Query vs DAX - If the question mentions data preparation or ETL, think Power Query. If it mentions measures or needs relationship context, consider DAX.
2. Understand M language basics - Recognize common M functions like Text.Combine, Text.Split, Date.Year, and Number.Round.
3. Remember the Transform vs Add Column tabs - Transform modifies existing columns; Add Column creates new ones while preserving originals.
4. Watch for data type questions - Changing data types is essential before performing calculations or creating relationships.
5. Conditional column scenarios - When questions describe categorizing or grouping data based on rules, think conditional columns.
6. Performance considerations - Power Query transformations typically perform better than DAX calculated columns for large datasets.
7. Recognize Column from Examples scenarios - When questions mention deriving patterns from data or when users show sample outputs, this feature applies.
8. Split column options - Know the difference between splitting by delimiter, by number of characters, and by positions.
9. Read carefully for context - Determine whether the scenario requires a one-time transformation or dynamic calculation needs.
10. Practice common transformations - Extracting year from dates, concatenating names, calculating percentages, and handling null values are frequent exam topics.