Transform data to include in a report in Power BI

Sometimes, the data you import into Power BI Desktop may contain extra data or be in the wrong format. Power BI Desktop includes the Power Query Editor tool, which can help you clean and transform the data so that it is ready for your models and visualizations.

This page covers "Transform data with Query Editor".

Launch Power Query Editor

To start, select the Transform data option from the Navigator window to launch the Power Query Editor. You can also launch the Power Query Editor directly from Power BI Desktop by using the Transform data button on the Home ribbon.

Screenshot of Transform data button.

After loading the data in Power Query Editor, you will find the following screen:

The four areas of the Power Query Editor screen.

  1. In the ribbon, the active buttons enable you to interact with the data in the query.

  2. On the left pane, queries (one for each table, or entity) are listed and available for selecting, viewing, and shaping.

  3. On the center pane, data from the selected query is displayed and available for shaping.

  4. The Query Settings window lists the query’s properties and applied steps.

Transform Data

When you right-click a column in the center pane of the Power Query Editor, a menu will appear with a list of available transformations. These transformations can be used to remove columns, duplicate columns, replace values, and split text columns into multiple columns.

Screenshot of the Change Type menu.

The Power Query Editor ribbon contains additional tools that can be used to change the data type of columns, add scientific notation, or extract elements from dates, such as the day of the week. As you apply transformations, each step will be listed in the Applied Steps list on the Query Settings pane. You can use this list to undo or review specific changes, or even change the name of a step. To save your transformations, select Close & Apply on the Home tab.

Here are some examples of the tools available on the Power Query Editor ribbon:

  • Change Data Type: This tool can be used to change the data type of a column. For example, you could change a column from text to number.

  • Add Scientific Notation: This tool can be used to add scientific notation to a column. This can be useful for columns that contain very large or very small numbers.

  • Extract Date Parts: This tool can be used to extract specific elements from a date column, such as the day of the week or the month.

Screenshot of the Query Settings dialog.

After you select Close & Apply, Power Query Editor applies the query changes and applies them to Power BI Desktop.


Did you find this article valuable?

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