How To Clean And Transform Data With The Power Query Editor?

Query editor is a function of Power BI desktop. This is a powerful tool for cleaning and transforming data so that your data is ready for your model and visual report. It is easy to use this process in Power BI desktop and easy to understand. If you need any change in your data using power BI then you can change using query editor. It works on two or more dataset.

Let’s discuss the following points as mentioned below:

1. What is data cleansing and transformation
2. Why use data cleansing and transformation
3. What is the Power query editor
4. How to use Power query editor

Data cleansing and transformation

Data cleansing is a process of removing and detecting inaccurate records from a recordset. It is used to alter data in a given storage resource to make sure that it is accurate and correct. Transformation is a process of filtering out unusable records of the database in power BI desktop. The data which is not useful to make a report and analysis, this type of data should be cleaned from our database.

Why use data cleansing and transformation?

When any data is in the incorrect format and gets some error while creating a visual report then we need to clean the data and transform it so that we can remove incorrect records from the dataset. When we import two or more dataset in Power BI then we need to manage relationships between two or more table and due to cleaning issue, this is not done. Then we get some different problem. Now in this situation, we follow the process of data cleansing and data transformation which resolve the issue using power query and save again to imported file. Because it can solve the issue and establish the relation easily.

Power Query Editor

Power Query editor is a functional language that drives to Power BI transformation. It allows you to easily discover and connect to data from public and corporate data source. It is useful to manipulate data according to the requirement if have many functions like add column, transform, manage parameter, advanced editor, properties, merge, split, remove column or row, format and query setting etc. Using these functions we can implement lots of operation.

Example: Go to the home Ribbon and click on edit queries.

After that open the edit window in Power Bi desktop. We can select any one file in one-time power BI for edit the query. When you move to the editor then you can perform many tasks using editor window function.

How to use Power query editor?

When you open the edit window for any table in Power BI then you can perform many operations if you need to change the data type of any column. Then right click on the column name and get the data type list of column. We can change data type from it.

Form this step we can change the data type of any column of the table. Using Power query editor you can perform the following steps on the table:

  • You can merge column using merge query.
  • You can add the column from Add column tab.
  • You can import new data using new source according to requirement.
  • You can also edit new dataset using the enter data table.
  • Create parameter and edit parameter from manage parameters.
  • You can give the reference, delete and duplicate from manage tab.
  • You can choose the column and remove the column.
  • You can keep row and remove the row.
  • You can split the column into two parts.
  • You can replace value.
  • You can create append new query.
  • You can transpose, revert and count rows.
  • You can change the format also.
  • You can make a connection to R script using Run R Script.
  • You can use condition formatting on data.
  • If you get new requirement and need to create code in Power BI You can write code in advanced editors.
  • You can manage query dependencies from the database.
  • You can set the setting of the editor according to requirements.
  • You can refresh and load the report using properties in the query editor.

Conclusion

Power query editor is the most important part of the Power BI desktop. You can perform many processes according to report requirement in this editor. If you want any change in data, you can update many options using it. When you have data in the incorrect format or unstructured format then you can apply this process for cleaning and transforming the data and can perform error handling technique. Many a time we face some issues in the formation of data and you can fix this type of issue using Power query editor. This is very useful to apply DAX function also. Thanks!

Leave a Comment