Definition:
Power Query Editor offers a user-friendly graphical interface that facilitates data manipulation and transformation. Its wide array of features includes filtering, sorting, grouping, pivoting, merging, and appending data from different sources.
A major advantage of using Power Query Editor is its ability to transform data with ease and simplicity, without requiring advanced coding or scripting skills.
Follow the steps as shown in the picture to open the power query editor.
As we click on transform data it will open a power query window where we can perform all the required changes.
Change the data type of the column:
- Select the table.
- Select the column and “Right-Click” on the left corner where data type is seen.
- A drop-down list will appear.
- Select the data type according to you from the dropdown.
Replace Null Value:
- Select the columns that contain the null value that you want to replace.
- Click “Replace Values.”
- Enter the value you want to replace in the “Value To Find” field.
- Specify the value you want to use as a replacement in the “Replace With” field.
- Click “OK” to complete the replacement process.
Remove a Column:
- Right click on the column header in left corner(where we see the column data type)
- From the appeared drop down click on remove.
Where to look for the changes applied in the dataset:
We can see all our changes sequentially(in the order we changed) in the applied steps
Changing the Table Name:
- The name of a table can be changed from both the options shown in the picture.
- First is, simply double-click on the table_name in the queries and enter the new name you want to use.
- You can also change it from properties.
Changing the Column Name:
- “Right Click” on the column heading, click on rename,there you go.
- Simply you can double click on the column heading, and can type the name best suit for the column.
Picture after changing the Column Name(Replaced small “r” with Capital “R”):
Sorting the Column values:
- By a simple click on the arrow in the right side of the column header.
- A drop down appears, we can choose either of the two given options.
- And then click on “OK”.
Undo the applied changes:
- By a simple click on the cross(shown in the picture) we can undo the changes which are no more required.
Conditional Column:
Once you have finished transforming your data, don’t forget to save the query. To do so, click the “Close & Apply” button located in the “Home” tab. This is the final step that will save and load your transformed data.
Conclusion:
Power BI Desktop and Power Query Editor are powerful tools for cleaning and transforming your data. With these tools, you can quickly and smoothly perform all the necessary data transformation actions and processes. As demonstrated in this blog post, everything can be achieved through a series of simple clicks. Once you become familiar with the tools, sorting and transforming data becomes effortless.