Step 3: Understand the Query Editor Layout
Before you begin editing your queries, it is better we spend some time understanding how the Query Editor is laid out:
1.Queries Pane: You shall see the name of every table or dataset that has been loaded into Power BI on the left side.
2.Data Preview: The center screen is the preview of the data you are working with, and it will let you see real-time modifications made to your data.
3.Applied Steps Pane: The right half contains a list of all the steps that have been applied to the data. These steps are recorded every time you perform an action on the data, such as filtering or adding a column. Should you choose to undo an application step, you can simply delete it from here.
Step 4: Editing Columns
Editing of data columns is among the commonly done tasks in the Query Editor. Whether you need to change the type of data, rename a column or split a column, the Query Editor makes it simple.
Let's go through a few examples.
Renaming Columns
1.Right-click on the column header you want to rename.
2.Select "Rename" from the context menu.
3.Type the new name for the column and hit Enter.
Changing Data Types
There may probably arise incidences when Power BI automatically applies an incorrect data type to a column (e.g., treating a date as text).
To adjust it:
1.Select the column for which you want to change the data type by clicking on its header.
2.Select the "Transform" tab in the ribbon.
3.Select the correct data type under "Data Type" in the dropdown (e.g.); Date, Whole Number, Text.
Step 5: Filtering Data
Power BI Query Editor has the property of filtering out unwanted data. This works best when dataset sizes are big since it allows focusing on specific rows of data.
To use this to filter rows:
1.Click on the down arrow next to the column header.
2.Choose the filter condition (e.g. equals, greater than, less than).
3.Apply the filtering conditions by choosing the relevant values or range.Type, number, or date conditions can be established to filter data, allows for less-than-or-greater-than data with the other visible filters.
Step 6: Merging Queries
Assuming working with multiple tables and looking for an easy way to combine them, this as per most, will be merging queries in Query Editor.
Here are the steps:
1.In the Query Editor, click on "Home" in the ribbon and select Merge Queries.
2.Select the first query (table) to be merged with the second.
3.Select the columns to match in both tables.
4.Choose join type (e.g. inner join, outer join, left join) as per how you want to combine the data.
This will add the data from the second query as new columns in the first query.
Step 7: New Columns Addition
But perhaps you want to create new columns for some calculations or data transformation. The options to create custom columns are given in Power BI Query Editor. Here is how to do it:
1.In the Query Editor, click on the Add Column tab.
2.Select Custom Column.
3.Write the formula to form a new column in the appearing dialog box.
If you want to calculate the total price by multiplying quantity and price, you can simple write a formula like this:
[Quantity] * [Price]
4.Click OK and the new column will appear in your data.
Step 8: Grouping Data
Grouping data is another helpful transformation in Power BI. You can aggregate data or compute totals or averages for a category using the "Group By" function.
Grouping is done as follows:
1.Select the column(s) you want to group by.
2.From the "Transform" tab, choose "Group By".
3.In the dialog box that appears, select the aggregation type, e.g. sum, average, count for the particular column(s) that will be computed.
4.Press OK.
Data will now be grouped and the results will be displayed aggregated.
Step 9: Apply Changes and Data Load
After making all the required edits to your data, it's time to apply changes and load them into Power BI:
1.Click on Close and Apply at the top-left corner of Query Editor.
2.Power BI applies all the steps done and loads the data into your report.
Step 10: Refresh Queries
Provided that, you have data updated outside of Power BI or you want data to be refreshing at any point in time, it becomes easy to do that:
1.Go to the "Home" tab in Power BI Desktop.
2.Click on Refresh to reload the data based on the changes done in the source.
That is sure to keep current data reflected in your reports.
