6 Commands To Get Started

Excel is a powerful tool for transforming and manipulating data thanks to its wide range of capabilities. Excel formulas and tools in the application. Add Power Query to this already powerful mix and you have an even more streamlined method for extracting data from different sources and transforming it on the go.

Advertising

If you’re new, Power Query can be intimidating at first. It takes you to a completely unfamiliar section of Excel with a different user interface and menu options. What’s more, your standard formulas no longer behave the same, and a new coding language is entering the scene: M-code.

However, once you get past the initial learning curve, Power Query becomes one of the most effective tools for performing batch transformations on large amounts of data, while fully tracking every step of data manipulation in the process. Here’s a detailed guide to the best commands to help you get started with Power Query and build a solid foundation.

Before diving into the commands, let’s first understand the basics of Power Query and how it works. While basic Excel allows you to create or insert a table into the spreadsheet itself, Power Query can connect to various sources to retrieve data. That’s why the first step to accessing Power Query is to go to the Data Retrieval and Transformation section of the Data tab and select a data source. Excel supports data retrieval from multiple data sources such as databases, web pages, CSV files, Excel, PDF and many other file types. A big benefit of using Power Query is the ability to connect and combine multiple data sources into one.

Advertising

Once you connect your data sources, you can “Load” or “Transform” that data. Loading data means importing data into an Excel sheet or Power BI report.

Transforming data means performing manipulations on data using the Power Query editor. Here you can use all the convenient Power Query commands. At the top of the Power Query editor there are various tabs that bring together all the important functions for manipulating data. On the left side is the Query section, which lists all the data sources connected to the editor. The formula bar under the various tabs contains formulas in M ​​language. In the center, you can find a preview of your connected data in tabular form. On the very right side is the Application Steps section, which lists all the manipulation steps you perform when converting data.

Advertising

If you’re just getting started, here are some of the best and most commonly used Power Query commands to help you get ready.

Filtering a column works in much the same way as with an Excel sheet, but with added convenience. While Excel requires filters to be applied to column headers first, Power Query applies filters by default. To filter a column, click the drop-down list next to the column headings to open a list of all the values ​​in the column. Uncheck the values ​​you want to filter and click OK to complete the filtering.

Advertising

You can also use text and number filters to filter the value according to predefined rules. For example, if you need to filter for people between the ages of 18 and 23, you can apply the Between filter in the Number Filters tab.

When you apply a filter, the formula bar is updated with the corresponding M query. You can also change the code directly from the formula bar to update the filter. Moreover, the filtering process should appear as an applied step in the “Applied Steps” section of the editor.

The Split Column feature is an extremely effective tool for splitting columns based on delimiters, number of characters, numbers, and even character types. For example, consider Excel Mailing List with a column containing addresses: “Address, zip code.” Now you want to split the address column into an address column and a zip code column.

Advertising

Since “,” is the common delimiter for all values ​​in the Address column, you can split the column using a delimiter. Alternatively, you can also split postcodes by number of characters if your postcode data contains a fixed number of characters.

To split columns, first click the column header to select the entire column. Then click the Split Column button on the Home or Transform tab and select By Delimiter. Specify a separator in the dialog box and click OK to separate the columns. You should now see two columns named “Address.1” and “Address.2”, which you can rename to “Address” and “Zip Code”.

Cleaning and formatting your data is just as important as manipulating it to make it visually appealing and consistent. Like most of the other commands mentioned in this list, formatting, trimming, and clearing values ​​use non-identical approaches in a regular Excel worksheet, but Power Query combines these important features into one menu item: Format. The Format feature in the Convert tab lets you use lowercase, uppercase, and capital letters for each word using buttons on the go.

Advertising

To use the Format option, simply select the column containing text values ​​and navigate to the Format option. Clicking on the lower or upper case options will convert the entire column so that it contains only the appropriate case. What’s more, the Capitalize Every Word feature essentially works similar to title case in Word, capitalizing the first letter of each word.

In addition to these basic formatting features, the Format feature offers trimming and data cleaning functions. If your data contains unwanted spaces between characters at the beginning or end of cell values, the Trim feature can automatically remove all of those spaces. Moreover, the cleanup function removes all non-printable characters from the data, making it printable.

Advertising

Removing duplicates is a common operation that must be performed frequently when converting data. The Remove Duplicates command keeps only the first occurrence of each value and removes the rest. Another important operation, Remove Whitespace, involves removing lines from the data that do not contain values.

Advertising

In a regular Excel worksheet, removing duplicates and removing spaces involve completely different methods. Power Query makes it easy to remove rows from your data based on various criteria and brings it all under one roof. To remove an unwanted row, select the column or columns from which you want to remove duplicates. Then go to the “Delete Rows” section on the Home tab and click the drop-down arrow. Now select the Remove Duplicates option and the selected columns should now only have unique values ​​for each row.

What happens if you only want duplicate values ​​in the columns? There’s a trick in the Power Query editor to hide you from using the “Save Rows” option.

Advertising

As the name suggests, the Keep Rows feature does exactly the opposite of the Delete Rows feature, and you can remove all unique values ​​using the Keep Duplicates option.

If you’re into data analysis, the Group By function won’t be new to you because it’s a fundamental SQL function. Power Query presents this complex but useful feature in a simplified and interactive way using dialog boxes and drop-down lists. Group By essentially transforms data based on one or more columns by re-evaluating it for one of the basic operations—sum, count, average, median, min, max, and all rows. While it sounds like rocket science by definition, Group By is a relatively simple command once you get the hang of it. Consider data containing the names, ages, countries, and salaries of different employees in an organization. The problem statement is to display the number of employees from each country in a base table containing the country name and the number of employees. To create this table, click the Group By option on the Home or Transform tab and wait for the Group By dialog box to appear.

Advertising

Now select the column by which you want to group the data. For the current problem statement, select the Country column and select the Count Rows operation. Now rename the new column to “Number of Employees” and click “OK”.

This basic Group By operation, which includes a single Group By column, is great for getting the gist of the function, but Power Query also offers an advanced Group By operation where you can group multiple columns together and perform more complex operations.

The formula bar in the Power Query editor is automatically updated with the M code corresponding to the applied data step. The M code may seem intimidating at first glance, but you can easily understand its syntax once you take a closer look. The Advanced Editor option on the Home tab opens a shortened M code editor for all applied steps to the data. You can write M code directly in this editor to add additional steps to the conversion. However, using M code to write entire steps can significantly slow down your workflow, especially for beginners. The best way to use M code is to modify the code to avoid changing the steps using the editor interface. Moreover, you can copy and paste similar M codes to perform the same actions at different stages.

Advertising

The Power Query Editor is an extremely powerful tool with a huge range of data transformation features. While these commands should get you off to a good start, Power Query can do a lot more with its merge feature, which lets you join tables between data sources, add different data sources, and more.