Introduction

Consider the scenario where you have imported data into Power BI from several different sources and, when you examine the data, it is not prepared for analysis. What could make the data unprepared for analysis?

When examining the data, you discover several issues, including:

  • A column called Employment status only contains numerals.

  • Several columns contain errors.

  • Some columns contain null values.

  • The customer ID in some columns appears as if it was duplicated repeatedly.

  • A single address column has combined street address, city, state, and zip code.

You start working with the data, but every time you create visuals on reports, you get bad data, incorrect results, and simple reports about sales totals are wrong.

Dirty data can be overwhelming and, though you might feel frustrated, you decide to get to work and figure out how to make this data model as pristine as possible.

Fortunately, Power BI and Power Query offer you a powerful environment to clean and prepare the data. Clean data has the following advantages:

  • Measures and columns produce more accurate results when they perform aggregations and calculations.

  • Tables are organized, where users can find the data in an intuitive manner.

  • Duplicates are removed, making data navigation simpler. It will also produce columns that can be used in slicers and filters.

  • A complicated column can be split into two, simpler columns. Multiple columns can be combined into one column for readability.

  • Codes and integers can be replaced with human readable values.

In this module, you will learn how to:

  • Resolve inconsistencies, unexpected or null values, and data quality issues.

  • Apply user-friendly value replacements.

  • Profile data so you can learn more about a specific column before using it.

  • Evaluate and transform column data types.

  • Apply data shape transformations to table structures.

  • Combine queries.

  • Apply user-friendly naming conventions to columns and queries.

  • Edit M code in the Advanced Editor.