Clean data to include in report in PowerBI

Clean data to include in report in PowerBI

Photo by Andrew Neel on Unsplash

Power BI can import data from almost any source, but it works best with columnar data. Columnar data is data that is organized in rows and columns, with each column containing a single type of data. Excel spreadsheets are often not formatted as columnar data, which can make it difficult to use Power BI to visualize and analyze the data.

In this article, you will learn how to use Power Query Editor to clean columnar data from an Excel spreadsheet. You will learn how to remove unnecessary columns, convert text to columns, and split columns into multiple columns.

This page covers, "Clean columnar data".

A table layout that looks good to the human eye might not be optimal for automated queries. For example, the following spreadsheet has headers that span multiple columns.

Excel spreadsheet with headers that span multiple columns.

Clean data

Power Query Editor has tools to help you quickly transform multi-column tables into datasets that you can use.

Transpose Data

By using Transpose in Power Query Editor, you can swap rows into columns to better format the data.

Screenshot of the Transpose button

Format Data

Sometimes, you may need to format your data so that Power BI can properly categorize and identify it. This is called data transformation. There are many different data transformations that you can use, but some of the most common include:

  • Promoting rows into headers: This transformation can be used to convert a table of data into a columnar format. This is often necessary for Power BI to properly understand the data.

  • Using Fill to replace null values: This transformation can be used to replace missing values in your data with a default value. This can be helpful for making your data more complete and consistent.

  • Unpivoting columns: This transformation can be used to convert multiple columns of data into a single column. This can be helpful for making your data easier to visualize and analyze.

    Power BI allows you to experiment with different transformations to see which ones work best for your data. You can also undo any transformations that you don't like. The Applied Steps section of the Power Query Editor records all of your actions, so you can easily undo any steps that you don't want.

Screenshot of how to remove steps from the Applied Steps section.


Did you find this article valuable?

Support Sweta_Sarangi by becoming a sponsor. Any amount is appreciated!