What Basic Transformations Can You Perform Using Power Query?
Let’s examine a few simple transformation methods in this part that just require a few mouse clicks to execute.
Read More: training excel power query
1. Functions for Text Formatting
This part will teach you how to utilize the Trim operation and format text in both uppercase and lowercase letters.
UPPERCASE
Step 1: Fill in the Power Query Editor with the necessary data. To accomplish this, choose the appropriate data source from the Excel Data tab’s Get & Transform Data section. This will launch the Editor, enabling us to make changes to the data.
Step 2: After selecting the column name, click the “Transform” tab to view a number of alternatives. When you select the Format text option, a drop-down menu with the text edit option “UPPERCASE” will appear.
Step 3: The text in the specified column will all be changed to uppercase after choosing the UPPERCASE edit option.
LOWERCASE
Step 1: Fill in the Power Query Editor with the necessary data. To accomplish this, choose the appropriate data source from the Excel data tab’s Get & Transform Data section. This will launch the Editor, enabling us to make changes to the data.
Step 2: After selecting the column name, click the “Transform” tab to view a number of alternatives. When you select the Format text option, a drop-down menu containing the text edit option “LOWERCASE” will appear.
TRIM
Step 1: Fill in the Power Query Editor with the necessary data. To accomplish this, choose the appropriate data source from the Excel data tab’s Get & Transform Data section. This will launch the Power Query Editor, enabling us to make changes to the data.
Step 2: Click on the column name, pick the “Transform” tab, which has a number of choices, and eliminate all the excess white spaces from the data. When you select the “Format” option, a drop-down menu with the text edit option “Trim” will appear.
2. Dividing a Column by Using Spaces
Step 1: Fill in the Power Query Editor with the necessary data. To accomplish this, choose the appropriate data source from the Excel data tab’s Get & Transform Data section. This will launch the Editor, enabling us to make changes to the data.
Step 2: Select the “Transform” tab and then the “Split column” option to divide the column using a delimiter from the data. A drop-down menu with the option to divide the data by delimiter will then appear.
3. Convert a Table of Data
Step 1: Fill in the Power Query Editor with the necessary data. To accomplish this, choose the appropriate data source from the Excel data tab’s Get & Transform Data section. This will launch the Editor, enabling us to make changes to the data.
Step 2: We must select the Transform tab in order to rotate the rows into columns. After choosing, a Transpose the Data option will be displayed.
4. Making Use of Power Query to Remove Duplicates
Fill in the Power Query Editor with the necessary data. To accomplish this, choose the appropriate data source from the Excel data tab’s Get & Transform Data section. This will launch the Editor, enabling us to make changes to the data.
The Window for Queries and Connections:
In Power Query, the Queries & Connections pane serves as a single center for managing and interacting with your connections and data queries. You can quickly access, change, and refresh all of the questions in your worksheet since it gives you a comprehensive overview of each one. The pane also shows the connections to other data sources, allowing you to add new connections, edit existing ones, and change connection parameters. For managing and arranging your Excel data connections and queries, the Queries & Connections pane is an invaluable resource.
What Separates the Add Column and Transform Tabs:
The Transform and Add Column tabs in Power Query are two essential areas that help with data processing. With the many data transformation options available on the Transform tab, you may filter rows, sort data, eliminate duplicates, and divide columns, among other operations. The Add Column tab, on the other hand, is dedicated to adding new computed columns to your data. It gives you access to a number of operators, functions, and formulae that you may use to build custom columns from preexisting data. Knowing the differences between these two tabs can help you use Power Query to alter and improve your data more efficiently.