Power Query formulas offer a wide range of possibilities for manipulating data in Excel and Power Query. Find out all you need to know about the different types and how to use them...
With Power Query, Excel and Power BI users can transform, cleanse, combine and query data from multiple sources.
Its intuitive interface makes data analysis accessible to as many people as possible. The query editor requires no coding, and this is what makes Business Intelligence so accessible.
The tool even offers automatic data cleansing tools for detecting and removing duplicates, correcting data errors, deleting spaces or normalizing dates.
What’s more, Power Query also offers automatic data cleansing tools such as duplicate detection and removal, data error correction, space removal, date normalization and more. These tools enable users to clean up data without having to write formulas.
However, to manipulate data more efficiently and accurately, advanced users can use Power Query formulas. These are written in the M language.
These formulas can be used to perform calculations, manipulate text, aggregate imported data or perform other operations such as merging, filtering, sorting or formatting.
Powerful and flexible, Power Query formulas enable you to customize data transformations to suit your specific needs. In this article, you’ll discover the different categories and how they work!
What is the M language?
The M or Power Query Formula Language is used in Power Query to describe data transformation steps. It is a functional language, based on functions that take arguments and return values.
Power Query uses this language to write formulas: expressions describing the data transformations to be performed. It’s a powerful tool for ETL and data analysis.
Its syntax is simple and intuitive, making it easy to learn for people already familiar with other programming languages.
The various M language functions are organized in libraries, grouped by theme and category.
What are the text transformation formulas?
Text transformation formulas such as LEFT, RIGHT, UPPER, LOWER, PROPER, SUBSTITUTE, TRIM, CONCATENATE and LEN are used to clean up and transform text data.
They make it easy to manipulate character strings such as names, addresses and telephone numbers. It is also possible to remove unwanted characters such as spaces, hyphens or parentheses.
For example, if a list of names contains unnecessary spaces, the TRIM formula can be used to remove them. Similarly, the formula SUBSTITUTE can be used to delete all spaces directly.
Date transformation formulas
To manipulate date and time data, we use date transformation formulas such as DATE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, NOW and TODAY.
These formulas are used to easily manipulate time data, or transform it into different formats as required.
For example, if you have a list of dates in different formats, the DATEVALUE formula can be used to transform them into a standard format.
Another use case is to extract the month from each of these dates. Simply use the MONTH formula.
Number transformation formulas
Numerical data manipulation is performed using Power Query number transformation formulas such as SUM, AVERAGE, MIN, MAX, COUNT and IF.
The aim is to calculate basic statistics, or to perform mathematical operations on numerical data.
Let’s take the example of a price list containing a column for each product. The SUM formula can be applied to calculate total sales for each product.
And to extract price data above a certain value, nothing could be simpler: you can use the IF formula.
Search and filter formulas
Within a list of data, it is sometimes necessary to extract specific information. For such tasks, you can use search and filter formulas such as VLOOKUP, HLOOKUP, INDEX, MATCH, FILTER and SORT.
They enable you to quickly find specific information in a table or other data set.
For example, on a list of product names, the VLOOKUP formula can be used to find the price of each one. SORT can also be used to sort the list alphabetically.
Merge and join formulas
To combine multiple data from different sources, you can use merge and join formulas such as JOIN, MERGE, APPEND and UNION.
This makes it easy to bring all data together in a single table. It’s the ideal way to get a complete overview.
Let’s take the example of a list of sales from many different regions. To get a clearer picture, you can use the MERGE formula to combine all sales into a single table. With the APPEND formula, you can add a new column to this table.
Pivot table formulas
Pivot tables are commonly used for data analysis in Excel and Power BI.
However, it is possible to create such tables from large quantities of data using formulas such as PIVOT, GROUP BY or AGGREGATE.
This can be useful, for example, if you have a large sales list and want to create a table to view sales by region. In this case, simply use the PIVOT formula. Similarly, the GROUP BY formula can be used to group sales by month.
How to use Power Query formulas
Here’s how to use Power Query formulas. First, open the Power Query editor in Excel or Power BI.
You can then load the data to be transformed using one of the loading options offered by Power Query, for example from a file or data source.
To add a transformation step using a formula, click on the “Add Step” icon in the Power Query ribbon.
In the “Formula” window, you can enter your formula using the M language. It can be based on existing columns in the dataset, but it is also possible to create new columns.
Once you’ve added the formula, click on “OK” to apply it to the dataset. You can then preview the results of the transformation in the Power Query window. All that’s left to do is save and load the data!
Conclusion: M formulas extend the possibilities of Power Query
Although it is possible to use the graphical interface of the Power Query editor, M-language formulas offer more possibilities.
However, to use them, technical skills are required. To acquire them, DataScientest training courses are the right choice.
Our dedicated Power BI course enables you to master the tool in just 5 days. You’ll learn about Power Query, the DAX language and DataViz, as well as more advanced concepts such as DataFlows and incremental updating.
By the end of the course, you’ll be able to use Power BI to collect and analyze data and create interactive dashboards.
As a Microsoft Learning Partner, our organization also prepares you for the PL-300 exam, leading to Power BI Data Analyst Associate certification.
All our training courses are carried out entirely online and remotely, and are eligible for funding options. Discover DataScientest!
Now you know all about Power Query formulas. For more information on the same subject, take a look at our complete guide to Power Query and our dossier on Power BI.
