Explain PowerBI query editor

Power BI is a Microsoft-developed data analytics tool used to visualize data and to find useful insights. In this post, you can see how to work with the PowerBI query editor in Power BI desktop. Power BI comes in various models including Power BI Server, Power BI Online, Power BI Mobile, and Power BI Developer. Power BI desktop is the free version, and all three versions of the PowerBI query editor are available.

Power BI Desktop has many components for storing, modeling and visualizing data, and also for producing data reports. The PowerBI query editor is used for transforming or editing data files before actually loading them into the Power BI.

To get in-Depth knowledge on Power BI you can enroll for a live demo on Power BI online training

PowerBI query editor

The PowerBI query editor plays the role of an intermediate data container where you can modify data by selecting rows and columns, splitting rows and columns, pivoting and unpivoting columns, etc. The changes made by Power BI 's PowerBI query editor are not reflected in the actual dataset. Once the data has been pre-processed and transformed into the required format, the data can be loaded to the Power BI environment.

In this post, you can see how to use the PowerBI query editor in Power BI desktop to convert data and then load it into the Power BI desktop.

Linking Power BI to a Data Source

You need to link Power BI to a data source before you can reprocess the data using PowerBI query editor in Power BI. Power BI can take data from different sources like MS Excel, Text / CSV, SQL Server, Web, etc. In this article you'll see how to link to a web-based data source.

To do so, open the Power BI Desktop and select the "Get info" button located on the left side of the dialog box from the Sign In dialog box shown below.

Get data

A window will appear that lists all the Power BI data sources. Enter "internet" in the search box. You can see the data source "Internet" listed in the list of All data sources. Select "Internet" and press the "connect" button, as seen in the screenshot below. Get more skills from Power BI training

You can see the dialog box below. In the URL field the following URL is entered:

https:/raw.githubusercontent.com/datasciencedojo/ master / titanic.csv

This URL points to a Github CSV-file. The file contains the Titanic dataset, which includes passenger information that sailed on the unfortunate Titanic ship that sank in 1912.

Online Data source

Depending on the file size, Power BI can take some time to establish a connection with the online data source. Once connection is established, the following window should be displayed:

If you click the "Load Button" at this point in time, the dataset will be loaded into Power BI. If you want to open the data in Power BI 's PowerBI query editor and do some pre-processing, you need to click the "Transform Data" button.

In the Question Window the data opened looks like this.

Open the downloaded data in PowerBI query editor

Along with the column form you can see all the columns in your dataset. The Name column, for example, has type ABC, meaning that the column contains text. You can actually click on the column form to see all types of columns and their names as seen in the screenshot below.

Working with the PowerBI query editor

Now that we have opened the data in Power BI 's PowerBI query editor, we can apply various pre-processing functions to the data. Let's look at some of those features:

●        Row removal and filtering

●        PowerBI query editor allows the removal or filtering of the rows from a dataset.

●        Remove Null-value rows

If you look at the Titanic dataset in the PowerBI query editor you can see that there are some null values in the Age column as shown below.Take your career to new heights of success with Power BI online training Hyderabad

You can delete rows in which Age includes null values. To do so, press the button on the extreme right of the Age column containing the inverted triangle. Then uncheck the "empty," as seen below, from the list of all unique values in the age column.

Columns choosing null values to delete

Click the OK button, and you can see the elimination of the rows in which the Age column includes null values.

Removing blank-value rows

Similarly, the same process can be repeated to delete the rows where the Cabin column includes blank rows, as seen below.

You must open the list of unique values for the Cabin column again and then uncheck the checkbox to the left of the option "blank."

In PowerBI query editor, select the rows with blank entries for deletion

Click the OK button and you can see the elimination of all rows where empty values are found in the Cabin column.

Through PowerBI query editor of Power BI, you can also undo the operations you perform on the dataset. In the "APPLIED STEPS" window in the screenshot below, you can see the operations you have performed on the data collection. By clicking the cross icon on the left of each stage, you can delete the operations.

Reviewing measures already taken in application settings

Condition-based filtering rows

You can also delete rows in a given column based on values. For example, you can use the Number Filter property if you only want to keep the rows in which the Fare column has values either greater than 40 or less than 10. To do so, click on the triangle to the left of the name of the Fare column and then click on the property "Number Filters." A drop-down list will pop up, from the list select "Greater Than," as shown below.

Filtering rows by definition

You can see the button below. In the respective fields, add 40 and 10 and press the OK button. You will see that the dataset now only contains the rows where the Fare column has a value of 40 or less than 10.

Defining the requirements for a filter

Filters can also be applied to the text column. For example, uncheck "male" checkbox from the Sex column filters as shown below if you only want to keep the Female passenger records.

Columns removed

Like rows, columns you don't want in your dataset can be omitted too. In the Titanic dataset, for example, the Cabin column contains several null values. We can have it removed. To do so, click from the top menu on the option "Choose Column-> Choose Columns," as shown below.

Choose the alternative Column

In your data set you'll see a list of all the columns. Uncheck the checkbox at the left of the column you would like to delete as shown below.

●        Pick the individual columns from a menu for deletion

●        Select the "OK" button and you will see the elimination of the Cabin column from the dataset.

●        Another way to remove a column is to simply select the column and then press the "Select Columns-> Remove Columns" option from the top menu, as shown in the two screenshots below:

●        Pick an individual column for manual deletion

●        Select Deletion for the previously selected columns manually.

●        You can rename a column, as well. To do so, simply double click the header in the column and write the column's new name. For eg, the Fare column was renamed to Ticket Price in the following.

Renaming a Power-BI board

Using the Power BI query editor, you can substitute values in a specific column. For example, to substitute the blank rows in the Cabin column containing a null value, first select the Cabin column and then click the Replace Value option from the top menu as shown below:

Conclusion

Power BI query editor is a very useful tool for transforming data before it's actually loaded into the Power BI framework. In this article we discussed some of Power BI's most common PowerBI query editor operations. You can learn more through Power BI online training India.