Custom columns in Power Query

Data Transformation in Power BI

Maarten Van den Broeck

Content Developer at DataCamp

Custom columns

  • Written in M language
    • Comes from data Mashup
    • Case-sensitive
  • Can reference several columns
  • Extend functionality of existing transformations
  • Implement advanced logic for conditional columns
    • Using AND OR NOT
Data Transformation in Power BI

Operations for custom columns

  • Numerical only
    • + - * / ^
  • Text
    • &
  • All columns
    • < > <> <= >= =
  • Conditional logic
    • if then else
    • AND OR NOT
  • Mind your data types
    • Mismatches will result in errors

A screen shot of the custom column window in power query, showing an example of the multiplication operand being used to compute total sales

Data Transformation in Power BI

Group by with custom columns

  • Using the aggregation "All Rows"

    • Divides data into several smaller tables
    • Use Table M functions on a column

  • Example:

    • Group sales data into product categories
    • Rank each product in each category

A screen shot showing a table that has been grouped with the All Rows aggregation.

Data Transformation in Power BI

Let's practice!

Data Transformation in Power BI

Preparing Video For Download...