Numerical transformations in Power Query

Data Preparation in Power BI

Maarten Van den Broeck

Content Developer at DataCamp

Why should you clean data?

  • Cost of bad data = $3.1 trillion
  • The 1-10-100 rule
    • $1 to verify
    • $10 to clean
    • $100 if you do nothing

Harvard Business Review logo

1 https://hbr.org/2016/09/bad-data-costs-the-u-s-3-trillion-per-year
Data Preparation in Power BI

What is clean numerical data?

  • Free from missing values / errors / outliers
  • Mathematical transformations are applied (if applicable):
    • Absolute value
    • Logarithm (Natural / Base 10)
    • Multiplying by / adding a scalar value
  • Data is rounded to the appropriate amount of digits
Data Preparation in Power BI

On date columns

  • Date (and time) is considered a separate data type in Power Query
  • Special transformations can be applied to a date column:
    • Extract year, quarter, month, week, day
    • Start/end of year, quarter, month, week
    • Extract age
    • Other

A screen shot showing the date transformation option in Power Query

Data Preparation in Power BI

Let's practice!

Data Preparation in Power BI

Preparing Video For Download...