Introduction to Power Query Editor
Power BI comes with a built-in data editor called Power Query which helps you to connect to sources and shape and transform the data to meet your needs.
In this article, we’ll focus on some of the features of power query and we’ll give some examples that showcase its capabilities.
What is Power Query?
Power Query is Microsoft’s Data Connectivity and Data Preparation technology. It basically, enables business users to access data stored in data sources seamlessly whilst, reshaping it to fit their needs. Using Power Query, you can perform the extract, transform, and load (ETL) processing of data.
What is Power Query Editor?
Power Query Editor plays the role of an intermediate platform where you can modify and filter it based on your needs. Once you have pre-processed the data and have transformed it into the required format, you can load the data into the Power BI environment.
Working with Query Editor:
To open the query editor, click on Transform data, or right-click on the table then click on edit query. this editor window will be open:
Removing and filtering rows:
Query editor allows you to remove or filter the rows from a dataset. Let’s try removing null values from the dataset. you can remove null by simply clicking on the arrow in the header and then untick null from the list of values.
Similarly, you can remove the rows with blank values. Furthermore, you can do conditional filtering by clicking on the header then choosing number filters:
Replacing values:
You can replace values in a particular column via the Query Editor in Power BI. For example, to replace the blank rows containing a null value. for my dataset, I will replace values in the Units column:
and then put the values you want to replace:
Splitting columns:
You can split one column into more using a number of different options. for example, in my dataset, I want to split the following text into columns.
Click on the column, then click on a Split column in the transform tab:
I will choose “comma” as the type of delimiter
Conclusion:
Query Editor of Power BI is super powerful and you can perform a lot of operations on your datasets to shape them based on your needs. In this article, we covered some simple and basic operations but you can definitely perform more advanced operations.
I hope this article was useful to you. See you at the next one!
Cheers,
Resources:
