M Language and Power Query Editor In Power BI

In this tutorial, you’ll learn about Power Query and M language and how to master them. This will also go over their uses inside the Power BI desktop. You’ll learn and understand their importance in developing and obtaining data for your Power BI reports.

The Power Query Editor

Power Query is designed for data transformation and data preparation. It comes with a graphical interface that is connected to a wide range of data sources. It also has an editor inside the Power BI Desktop called Power Query Editor that transforms raw data.

Raw data is transformed through the process called ETL which stands for Extract, Transform, and Load. The process extracts data by connecting to the data sources, then transforms them through reshaping and cleaning. The last process loads the data to the data model.

m language

The goal of the Query Editor is to help the user apply common transformations by interacting with the ribbons, menus, and other components. These transformation capabilities are common across all sources. So, the user experience inside the Query Editor remains the same no matter what the source is.

Once you connect to a data source, Power Query will show you a preview of the data. The preview is based on a subset that allows you to work with large datasets and design your query. The full data will only be provided once you click Close and Apply. The destination where the data is stored depends on what Power Query is used.

You can apply the same general techniques in Excel, Data Flows, Analysis Services, and other products where Microsoft has embedded the Power Query Engine. The Power Query Engine is available in many products and services.

The M Language

M is a functional, case-sensitive, mash up language that is designed for data transformation. When you perform a transformation through the UI, the code for that step is automatically generated. So, you don’t need to write any M code.

However, there are some things that you can only do by writing M such as creating custom functions, performing more complex transformations, optimizations, and even building custom connectors.

Now, with the introduction of IntelliSense, case sensitivity begins to have less impact. IntelliSense lets you call members of the intrinsic #shared, such as the following:

  • All native M functions
  • Custom functions
  • Variables or step names
  • Parameters in the Power Query instance

One of the most productive ways when using the user interface is to build and approach the query that you want. Then, go to the formula bar or Advanced Editor to modify the code.

To learn and master M, you need to understand the flow of the language. It will help you read and write codes easier, and improve your data development skills significantly.

Make sure that the formula bar is visible in the user interface at all times. This allows you to see the M code that’s generated and exposes you to its flow and syntax.

There are online resources available like the Formula Language Specification which describes the structure of the language, and the M function reference that lists all the functions and their documentation. Power Query also contains a standard library accessible through the #shared keyword.

***** Related Links *****
Power Query/M Masterclass #1 – New Course on Enterprise DNA

M Code In Power Query Custom Columns | Power BI
Power Query Function In Power BI: Group By Function

Conclusion

Learning how Power Query and M language operate is one of the best ways to master data development. If you know your way around all the available features inside the Power Query Editor, building your data report will be easier.

Melissa

Enterprise DNA Power BI On-Demand

The Latest

If you’ve been working with Python for data analysis or machine learning, you’ve likely come across NumPy arrays. They’re a powerful tool for handling numerical data, but sometimes, the data…

How to normalize a numpy array a quick guide.

Multiplying lists in Python is a common operation when performing mathematical computations or solving problems in data manipulation. There are multiple ways to achieve this task, depending on your requirements…

How to Multiply Lists in Python: A Simplified Guide

If you’ve been wrestling with Python lists and wondering how you can save them as a neat CSV file, you’re in the right place. One of the most common tasks…

How to Write a List to CSV in Python

Do you need to write an essay on the fall of the roman empire with accurate citations but have no time to find them? Are you tired of painfully having…

What is Caktus AI: A comprehensive overview

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality that pandas can provide you is the ability to modify the structure of a dataset….

Pandas Drop Index: Efficiently Remove DataFrame Rows or Columns

Working with strings is a common task in Python. You often need to figure out whether a string contains another string. The simplest methods to check if a Python string…

7 Ways to Check if a Python String Contains Another

Ever found yourself knee-deep in Python files, struggling to import a module from a parent directory? If so, you’re in good company. Python’s import system can sometimes feel like a…

Python Import from Parent Directory: A Quick Guide

Data is the backbone of businesses these days, and having proof that you know how to handle and make the most out of data is a big deal in the…

Microsoft DP-500

In programming, you may encounter situations where a variable does not have a value. These instances are commonly represented by a special value known as null. Many programming languages, such…

Null Python: 7 Use Cases With Code Examples

Truncating a floating-point number in Python is a common operation that many programmers encounter in various applications. The process involves removing the decimal place of a float, leaving only the…

Python Truncate Flow: A Streamlined Approach for Efficient Code Execution

OpenAI’s ChatGPT tool has taken the world by storm and has been at the forefront of revolutionizing the way we generate content, do research, and even create code. And just…

Is ChatGPT worth it, let's work it out.

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and easy to maintain. The Python programming language comes with a…

Python Inline If: Simplify Your Conditional Expressions

Load More