_edited_edited.png)
What is Power Query editor in Power BI?
Power Query is a data transformation and data preparation engine. , where you can connect to a wide range of data sources and apply hundreds of different data transformations by previewing data and selecting transformations from the UI.
As the name suggests, Power Query is the most powerful data automation tool found in Excel 2010 and later. Power Query allows a user to import data into Excel through external sources, such as Text files, CSV files, Web, or Excel workbooks, cloud, SQL to list a few. The data can then be cleaned and prepared for our requirements. Using Power Query, you can perform the extract, transform, and load (ETL) processing of data.
Power Query Editor is a powerful tool that allows you to transform and clean data before importing it into Power BI. It provides a user-friendly interface that makes it easy to create complex data transformations without writing any code.
How to Access Power Query Editor in Power BI
Open Power BI Desktop.
In the ‘Home’ tab, click on Get data (or) Excel work book (or) SQL server data to load the data.
3. Here we get the data from the related source we selected and then it shows to ‘Load’ and ‘Transform data’, where we can click on transform data to clean data, merge tables, remove duplicate columns (or) null values and its go on.
The Basics of Power Query Editor in Power BI
The tool offers a wide range of data transformations, including filtering data rows, merging columns, removing duplicates, and pivoting tables. One of the key features of Power Query Editor is its ability to handle large datasets. The basics of Power Query involve importing, transforming, and combining data from various sources in Excel, allowing users to clean, reshape, and analyze data without complex formulas, the four phases of Power Query are:
1. Connect:
In this phase, users connect to the data source(s) from which they want to extract data. Power Query supports many data sources, including databases, files, web pages, and more.
2. Transform:
Once the data is loaded into Power Query, users can use various data transformation tools to clean, reshape, and transform the data to meet their specific needs. Common data transformation tasks include removing duplicates, filtering data, merging data, splitting columns, and pivoting data.
3. Combine:
Power Query also allows users to combine data from multiple sources using various techniques. Users can merge tables, append, or join data using a common key.
4. Load:
Finally, in the Load phase, users specify to load the transformed data by clicking on Close & Apply. They can load the data into an Excel worksheet or a Power BI report or create a connection to the data source so that the data is automatically refreshed whenever the source data changes.
There are six main sections of the Power Query Editor are as follows:
Query Editor Ribbon: This ribbon is similar to the one on the Excel interface. Various commands are organized in separate tabs.
Query List: This section lets you browse through a list of all queries in your current workbook.
Formula Bar: The current transformation’s formula will be specified here in the M language.
Data Preview: You can see the preview of your data based on the current transformation step..
Properties: This section consists of a list of query steps. Here, you will be able to name your query. Naming a query is an important step to identify a query easily.
·Applied steps: Each transformation step you take will be recorded here in chronological order. You can add, remove, edit, or reorder the steps if required.
What Basic Transformations Can You Perform Using Power Query?
In this section, let’s look at various transformation functions that can be performed easily with the help of a few mouse clicks.
1. Text Formatting Functions
In this section, you will learn how to format text in Uppercase, Lowercase, and understand how to use the Trim operation.
UPPERCASE:
Click on the column name and then go to the “Transform” tab, which will display a variety of options. Clicking on the option to Format text will open up a drop-down menu with a text edit option of ‘UPPERCASE’. On selecting the UPPERCASE edit option, all the text in the given column will be converted to uppercase.
LOWERCASE:
Clicking on the option to Format text will open up a drop-down menu with a text edit option of ‘LOWERCASE’. As you can see, all the text from the selected column will be converted to lowercase.
TRIM:
To remove all the extra white spaces from the data, click on the column name, and then select the ‘Transform’ tab, displaying various options. Clicking on the ‘Format’ option will display a drop-down menu with a text edit option called ‘Trim’. On selecting the Trim edit option, all the extra white spaces in the given column will be removed.
2. Merging 2 or more columns:
For merging 2 or more columns first select columns using SHIFT and go to TRANSFORM menu and click on MERGE Columns, then a pop window will where we need to select Space in separator as it is a naming convention and need to separated by space and new column name should be defined as per the column is a Full name then click OK.
3. Splitting a Column Using Delimiters:
To split the column with the help of a delimiter from the data, click on the ‘Transform’ tab followed by the ‘Split column’ option. This will display a drop-down menu with an option to split the column By Delimiter. Now, we can see that the data is split into two columns concerning the delimiter.
4. Removing Duplicates Using Power Query:
After loading the data from different sources, there might be some duplicates which will be updated by mistake, and to remove the duplicates we can click on to Home tab — ->Remove Rows option — -> click onto Remove duplicates.
5. Using Conditional Column:
With Power Query, you can add a conditional column to your query. You can define IF-THEN-ELSE conditions in your query. When the conditions are fulfilled.
Conditional columns are columns that are added to your dataset based on specific conditions. These conditions can be things like a value in another column or a combination of columns’ values. When the conditions are met, the new column is added, and the data is populated based on your specifications.
Adding Conditional column for the below data on the Salary, explains that the salary is below 50,000 indicated as Low Income and less than or equal to 70,000 indicated as Avg Income and greater than 70,000 indicated as High Income.
In Power query editor Goto Add Columns and click on Conditional column
The Conditional Column for such logic would be like this:
Building the logic above i you choose the Column Name, then the Operator, and then the Value. After that, you can set the Output.
Conclusion:
In this article, you have learned how to load data using Power Query, perform transformations, and output the data back to your Excel worksheet. Using the Power Query tool, you are saving loads of time by performing numerous functions just with the help of a few clicks!