POWER QUERY EDITOR

1
What is Power Query?
• Power Query is a business intelligence tool designed by Microsoft for Excel.
• Power Query allows you to import data from various data sources and will enable you to clean,
transform and reshape your data as per the requirements.
• Power Query allows you to write your query once and then run it with a simple refresh.

• The Power Query Editor is a part of Power BI Desktop.

2
The list of data transformation operations we will study is as follows:
1.Rename operation
1. Rename data source
2. Rename columns
3. Rename query
2.Change the datatype of the column operation
3.Format operation
4.Use the first row as the header operation
5.Removal operation
1. Remove rows operation
2. Remove columns operation
6.Merge operation
7.Replace value operation
8.Split column operation
9.Pivot column operation
10.Unpivot column operation 3
Why is data transformation required?

• In the scenarios when we upload data from varied sources be it the internet, any Excel file or SQL
server, or any other source, generally the uploaded data is not in the desired format.

• So, in order to perform functions we first need to transform the data by either deleting some unwanted
rows and columns, by splitting the columns, by formatting, or by making certain required changes.
Therefore before loading the data directly on Power BI Desktop, we load the data on Power Query
Editor to make the required transformations.

• After the data is transformed it’s then loaded to the Power BI Desktop to create final reports of the
data.

4
Launch Power Query Editor:
Under the home menu bar, there’s an option “Transform Data” that provides the query editor feature. On
clicking the option transform data it navigates us to a new window called power query editor.

Now we will add data to query editor to perform desired operations.

5
Importing data on query editor :
• On the query editor window, we can see a new source option under the home menu.
• Under the new source option, we are provided with the option to add data either from an Excel file,
SQL server, or from the web, and so on.
• The data referred to here for illustration purposes is an Excel file named My movie list.xlsx and its data
is as follows:

6
On loading this data on the query editor it appears as:

Now, since we have the Excel sheet and data imported on the power query editor as well, we are in a state to
perform transformations.

7
Rename Operation in Power BI
The power query editor provides us with a variety of possible renaming. We can rename the data sources
or tables, columns, and queries. We will look into each of them one by one.
Rename Data Source :
It can be achieved by right-clicking on the source and opting for the rename option. Here, we have
renamed the data source from sheet 1 to Movie Data. On transformation, we get :

8
Rename Column:
• It’s also done by right-clicking the column in which you wish to change the name and selecting the
option of renaming and renaming as per the wish of the user.
• Here, we have renamed the column “TITLE” to “MOVIE NAME”. On renaming the column it appears as :

9
Rename Query:
• Under the query settings pane an “Applied Steps” section under which all the changes we made are stored as
queries. Using the query editor we can rename the queries also.
• Here, we have renamed the query “Renamed Columns” to “Columns Name Updation”. On renaming it appears as :

10
Make First Row As the Header in Power BI :
This operation is used to set the first row as the column header. This option is available under the
“TRANSFORM” of the ribbon. It has another option of “Use Headers as First Row” as well.

11
Change Data Types of a Column in Power BI:
• We can change or modify the data type of any column by right-clicking on the column which is followed
by a drop-down under which we have to select the option change type which further provides us with
varied suitable data types.
• Here, we have modified the data type of column “RANK”. Earlier, it was of “Whole Number” type which
has been modified to “Decimal Number” by us. This can be observed from the image below as well,
where before implementing the operation the numbers mentioned prior to the RANK are 1,2,3 i.e.
symbolizing whole numbers. Whereas after the operation is being done those (1,2,3) changed to 1.2 i.e.
indication of decimal numbers.
• This same operation can also be achieved from an option present in the home bar of the ribbon as “Data
type:<type>”.

12
13
Format Operation in Power BI
• The Format feature is provided in the “Transform” tab of the ribbon. It’s useful in formatting the text.
We have further options for formatting the text like we can change the data to lowercase or
UPPERCASE, also we can add prefixes or suffixes.
• Formatting has other options as well like trim and clean. Trim is used to remove leading and trailing
whitespaces for each data entry of the specifically selected column.
• Clean is used to remove nonprintable characters of selected columns. Performing all these operations is
very simple as it just involves clicking over the option.
• And so, we have performed only UPPERCASE operation here. Rest all can also be done in the same
fashion.

14
15
Removal Operation in Power BI:
Remove Row Operation:
• The removal of rows operation is supported by the “Reduce Rows” block. This feature is present in
the “Home” bar.
• The removal of rows operation has several sub-operations like removing rows(removing the top,
bottom, or alternate rows) and columns, removing duplicates, removing blank rows, and removing
errors.
• Here is an illustration performing the removal of the bottom-most row as follows.
• Rest all removal operations can also be performed in the same fashion.

16
17
Remove Column Operation in Power BI:
• The removal of columns operation provides us with the feature to remove columns or multiple
columns.
• Remove column is present in the “Home” bar. The “Remove Columns” feature is supported by the
“Manage Columns” block.
• On clicking Remove columns it provides whether you want to remove the selected column by clicking
on “Remove Columns”. We can also remove the rest of the columns other than the selected one by
selecting the option “Remove Other Columns”.
• Here, we will illustrate the “Remove Columns” by removing the column “RATING”. After the operation
will be performed “RATING” will not exist anymore.
• The “Remove Other Columns” can be performed the way.

18
Output:

19
Manage Columns block has two functions :
1.Choose Columns
2.Remove Columns .
• The 1st “Choose Columns” helps us to keep whatsoever columns user wants to keep. On the other hand,
2nd “Remove Columns” helps user to remove columns user doesn’t need anymore. The “Remove
Columns” operation is illustrated as above.
• The “Choose Columns” can also be done the same way. Both the features perform opposite operations
to each other. One helps to select particular columns(1) while the other helps remove specific columns
based on user’s needs.

20
Merge Operation in Power BI:

• We can merge multiple columns.

• To select multiple columns hold down the Ctrl key, navigate to the columns you want to be selected using
the left and right arrows, and then press the Space bar to actually select those columns.

• The “Add Column” bar supports the “Merge Columns” feature which is followed by a prompt of merge
columns that asks for the name of the merged column and to set the separator.

• Here, we have merged the columns “GENRE” and “RATING”. We have used a custom separator ” ”
and kept the name of the merged column simple as “Merged”.

The illustration along with the output is as follows :

21
Output: Merged Column

22
Replace Values Operation in Power BI:
Replace values operation replaces some specific value to our desired value. It’s present in the “Transform”
bar as “Replace Values”. Here, we have replaced “null” to “geeksforgeeks” for column “GENRE”.

23
Output:

24
Split Column Operation in Power BI:
• It’s present in the “Transform” bar on the ribbon. In the transform bar lies an operation as “split column”. We can
split either by using a delimiter, by providing no. of characters or positions, and so on.
• Here, we have split the column “Merged” using the customized delimiter ” ” that splits the column into two
columns “Merged.1” and “Merged.2”.

The illustration is as follows :

25
Before: After:

26
Pivot Column Operation in Power BI:
The pivot operation basically turns rows into columns. By default, the query editor does sum as aggregation which
can also be set as don’t aggregate or minimum or maximum or whatever as per the user’s wish from available
options. It is present in the “Transform” bar.

27
• In the output we see 9.3 corresponding to 2 and not multiple values such as 1.1, 4.3, and 3.9. It’s
because we have set the aggregate value function as sum i.e. it will sum all the values of 1.1, 4.3, and
3.9 which is “9.3”.
• Also, for 3 and 9 we have no repetitions so data corresponding to them is shown as “2.6” for each
respectively.

28
Unpivot Column Operation in Power BI:
• Unpivot column operation as the name also suggests does the opposite of what pivot does. Unpivot basically
unpacks similar values and gathers them under one label.
• When we did unpivot on the same data we took for the pivot operation it produces the output:

29
Output:

30
31