What is M Query?
M is the powerful language that powers Power Query. Any transformation you use will be written in M. Many people regard M as a frightening language. We want to show you how the syntax of the M language is structured. Everything becomes simple once you understand the syntax. M Query is a simple language with a simple syntax. We strongly advise you to spend time on M Query because there are so many operations that you can perform with M Query that you might not be able to do with the graphical interface and the dashboard.
M is an abbreviation for Power Query Formula Language. So, the formal name is so long that no one uses it; instead, everyone refers to it as M! Some say M stands for Data Mashup, while others say it stands for Data Modeling.
M is a functional language, and it is critical to understand its functions. Each language, however, has a structure and syntax that represents the beginner level of learning that language. We would like you to read this sentence aloud before learning M Query.
M Query is far more powerful than Power Query’s graphical interface.
You read that correctly! The graphical interface of Power Query changes every month. Every month, new features are added to this graphical interface. However, these features have been present in the language for many years! Instead of waiting for a graphical interface option, if you know the language, you can easily use them.
Syntax of M Query
Before I get into specific rules, I want to point out that M is case-sensitive in variable and function names. For example, you cannot write “IF” when writing the “if” function. Case sensitivity is crucial.
- In Excel, standard formulas can be written in capitals or small letters; however, Excel converts everything to capitals. For example, “if” is replaced with “IF.”
- It is acceptable to use small or capital letters in DAX.
- VBA automatically changes the syntax – in functions, mainly to the first capital letters.
So, you must be careful with the case sensitivity in M Query.
Basic Logic
The basic logic is straightforward. Two blocks are always present: “let” and “in.” “Let” has multiple rows that define variables, and “in” is an output. Yes, “in” refers to the output.
It appears as follows:
let
my_variable = “hello world”
in
my_variable
In this example, the output is “hello world,” which is the value of the variable “my variable” in the output. If you type this into the advanced editor in Power Query or Power BI, the text string “hello world” will appear.
Comments
Comments, or code that does not affect the query, can be written with two slashes (if there is only one row):
// this is a comment
Or in this way (if the comment is going to cover multiple rows)
*/ this is a
comment */
Why You Should Learn M QUERY
PowerQuery M is appropriate for beginners. Below are the reasons why you should learn it:
Built-Right Into Excel
There is no need to install any additional software because it is built into Excel and PowerBI. PowerQuery will be updated if you update Excel or PowerBI.
If any updates and changes break your solution, you can easily undo and redo steps to achieve the same result. PowerQuery will automatically update the code.
Your Data Is Completely Safe.
PowerQuery operates on a duplicate of your data. As a result, you can code your solution without concern. You can always go back to your original, unaltered data.
It also allows you to undo and redo steps easily, so you can make mistakes as you learn.
PowerQuery M Language Is More Fun Than VBA.
VBA was the first programming language built into Excel and is still used today. However, for anyone who has used VBA before, the quirks of the language (arrays that are fixed in length by default, dictionaries that must be imported from another library, etc.) combined with an outdated editor make it challenging to use. PowerQuery is unique. It is a well-thought-out and user-friendly language.
It Encourages the “Hacker” Mindset
The combination of editing and GUI code generation allows the user to experiment with various combinations of data transformation steps. As a result, the user can work around the code until their data looks correct.
Because of this combination, the user can arrive at a faster solution, reinforcing the hacker’s mantra “Make it work, make it fast/beautiful.” The beginner can use the GUIs to get the solution to work, then switch to editing mode to improve the code by removing unnecessary or duplicate steps.
Allows the User To Focus on One Thing
General-purpose programming languages include Python, Java, and others. This means they can be used to build web applications, GUIs, data science, scraping, and much more. This is great in general, but beginners are tempted to jump from one application area to another without first solidifying their knowledge in one. And, with so many tutorials available, it is entirely feasible.
PowerQuery is designed exclusively for data transformation and manipulation within PowerBI and Excel. This limitation, for some, is a blessing for a complete programming novice.
So, this aspect of M Query forces them to master one programming language until they are ready to move on to another.
Allows the User To Achieve Programming Self-Sufficiency
The ultimate goal of programming education is to achieve programming self-sufficiency. This means that the user, in general:
- Is at ease with seeing and reading code — understands that interacting with code will not break the computer and is highly forgiving.
- Knows how to find a solution on their own — this may entail searching around Googling a lot, StackOverflow, or using the in-built help.
- Knows how to set up their tools — programming has evolved to the point where tools are plentiful and simple to use/
As a result, learning PowerQuery will give any beginner the confidence to learn other languages later.
Breaks the Misconceptions About Programmers
Any novice has the impression that programmers are the ones who wear hoodies and spend all night coding in the command prompt in a dark room with multiple glowing screens.
This is totally not the case.
Anyone can code in their PJs using low-code tools like PowerQuery! Without all of these stereotypes, it’s easier to get started.
Why You Shouldn’t Learn M Query
Power Query is simple to use and appears to be Microsoft’s direction with their BI reporting. Power Query is simple to learn and use for business analysts, but you should proceed cautiously. In many cases, you will require a solution that allows you to limit the data in your reports using simple parameters and a way to manage the queries you write on a global level.
Power Query lacks a user interface and a simple method for passing parameter values to your query when retrieving data. When it comes to big data, the ability to pass parameters is a must. This means you won’t be able to use Power Query to analyse your transactional data effectively (ex., GL or Checks).
FAQ
Is Power Query and M Query the same?
M language is a Power Query mashup language. M language is an abbreviation for Data Mashup or Data Modeling. The M language, similar to F# in functionality, can be used with Power Query in Excel, Get & Transform in Excel 2016, and Power BI Desktop.
How do you do M Query in Power Query?
Steps: Power BI Desktop > Home tab > Edit Queries > select one of your queries > View tab > Advance Editor. A new popup window will open with the M-code of the selected query. Power query has an easy-to-use and powerful GUI tool.
What is M in Power Query?
Power Query’s core capability is to filter and combine data from one or more of the many supported data sources. The Power Query Formula Language is used to express any such data mashup (informally known as “M”).
Does Power Query use DAX or M Query?
People generally calculate measures and KPIs and use DAX to filter for visualization. Power Query M is used for ingestion and data wrangling. DAX for data analytics.
It is not possible to fully understand Data Analysis Expressions in a single day. Although you can quickly begin writing some basic code, it will take time to understand how the various filter contexts interact. It would help to determine how much effort you are willing to put in and then make an informed decision.
Conclusion
Microsoft Power Query offers a robust “get data” experience with numerous features. Power Query’s core capability is to filter and combine data from one or more of the many supported data sources. The Power Query Formula Language is used to express any such data mashup (informally known as “M”). Power Query embeds M Query documents in various Microsoft products, including Excel, Power BI, Analysis Services, and Dataverse, to allow for repeatable data mashups.