---
title: "Guide To M Code in Power BI: A Power Query Formula Language"
description: Explore key functions and master techniques (both simple and advanced) with the help of practical examples.
image: https://blog.dataddo.com/hubfs/M%20code%20PBI%20preview%20image.png
---

[![Dataddo Blog](https://blog.dataddo.com/hs-fs/hubfs/blog-logo.png?width=180&name=blog-logo.png)](https://blog.dataddo.com)

**

[Get Dataddo Free](https://www.dataddo.com/signup?hsLang=en)

[![Start for Free](https://no-cache.hubspot.com/cta/default/7885580/42e092dc-d403-4ac3-bd34-06dc490bf6d9.png)](https://cta-redirect.hubspot.com/cta/redirect/7885580/42e092dc-d403-4ac3-bd34-06dc490bf6d9)

# Guide To M Code in Power BI: A Power Query Formula Language

By Haziqa Sajid | 15 min read

![](https://blog.dataddo.com/hubfs/M%20code%20PBI%20preview%20image.png)

- **
- **
- **

Power BI is a powerhouse for analyzing and visualizing data. One of the features it offers is **Power Query**, which** **allows users to import, transform, and consolidate data from various sources into a more usable state. It can be accessed through the Power Query Editor, a graphical user interface, or Power Query M, a functional programming language.

This article will focus on how to use **Power Query M**.

Click to skip down to a specific section:

1. [What is M code?](https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language#WhatIsM)
2. [M code setup](https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language#MCodeSetup)
3. [Power Query M functions: Key points](https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language#MFunctions) 
     1. [Custom functions](https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language#CustomFunctions)
     2. [Built-in functions](https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language#BuiltInFunctions)
     3. [Function libraries](https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language#FunctionLibraries)
     4. [Advanced data transformations](https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language#AdvancedDataTransformations)
     5. [Query dependencies](https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language#QueryDependencies)
     6. [Query folding](https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language#QueryFolding)
4. [Why use Dataddo to get your data to Power BI?](https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language#WhyDataddo)
5. [Additional Power BI tips and tricks](https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language#Tips-Tricks)

 

## What is M Code?

M code is a [mashup language](https://learn.microsoft.com/en-us/powerquery-m/m-spec-introduction) designed to create queries that combine data. It is similar to the F# programming language and is described by Microsoft as a "mostly pure, higher-order, dynamically typed, partially lazy, functional language." M is also case-sensitive, so, for example, words like **Source **and **source **are considered different.

M Code allows users to write custom scripts to modify and shape data, and offers automation and fine-grain control for data cleaning, restructuring, and consolidation. Let’s see its syntax.

### Syntax

Here is how to say "hello world" in M code:

| ``` let // Start of the block  Source = Text.Proper("hello world")  // Functionin   Source ``` |
| --- |

- **let**: Starts a block to define variables and their values.
- **Source**: Variable holding the result of the following expression.
- **Text.Proper("hello world")**: Converts "hello world" to "Hello World".
- **in**: Specifies that Source is the final output of the query.

If this seems confusing, don't worry. We will discuss examples below to help you grasp M code.

 

## M Code Setup

You can view M code in Power Query Editor by clicking Transform Data in the ribbon, which will open the editor.

![Opening Power Query Editor in Power BI](https://blog.dataddo.com/hs-fs/hubfs/M%20code%201.png?width=1661&height=517&name=M%20code%201.png)

M code will appear in the Formula Bar, as shown below:

![M code in Power BI formula bar](https://blog.dataddo.com/hs-fs/hubfs/M%20code%202.png?width=1357&height=137&name=M%20code%202.png)

You can also open the Advanced Editor window to view it.

![View M code in Power BI's Advanced Editor window](https://blog.dataddo.com/hs-fs/hubfs/M%20code%203.png?width=985&height=215&name=M%20code%203.png)

Now, let’s build some understanding of Power Query M functions.

 

## Power Query M Functions: Key Points

It's impossible to cover all of Power BI's Power Query M functions in this article, but the selection below will give you a good taste of how they work.

 

### Custom Functions

Power Query M lets you define custom functions to bundle and reuse specific data transformation steps. These functions can be written within a query or in separate modules. 

The function's body comes after the => symbol, and you can optionally specify type information for parameters and the return value. Functions are defined and called within the body of a ‘let’ statement. Let’s define a simple function that multiplies the input by 2.

| ``` let    doubleValue = (x) => x * 2,    out = doubleValue(2)in    out ``` |
| --- |

 

The output for the above function will be 4.

 

### Built-in Functions

As stated above, a function maps input values to a single output. It's written by naming the parameters and providing an expression to compute the result. Here’s an example:

| ``` let      AddOne = (x as number) as number => x + 1,      //additional expression steps      CalcAddOne = AddOne(5)  in      CalcAddOne ``` |
| --- |

 

Writing your functions can be tiresome; therefore, Power Query provides many built-in functions, and here’s a complete [list](https://learn.microsoft.com/en-us/powerquery-m/power-query-m-function-reference). Here’s an example of two built-in functions:

| ``` let    out = Text.Proper("hello, world!"), // Capitalize the first letter    currentDayOfYear = Date.DayOfYear(DateTime.LocalNow()) in    [hello = out, currentDayOfYear = currentDayOfYear] ```   |
| --- |

 

**Text.Proper(): **Capitalizes the first letter of each word in the given text value.

**Date.DayOfYear(): **Returns the day of the year from the given date, datetime, or datetimezone value.

Here’s the result of the above query:

![Result of a built-in function query in Power BI](https://blog.dataddo.com/hs-fs/hubfs/M%20code%204.png?width=1374&height=299&name=M%20code%204.png)

 

### Function Libraries

Power Query M allows functions to be grouped into libraries or modules to enhance code organization and reusability. Libraries house collections of related functions that can be shared across various queries or workbooks.

Below is an example of a library of functions in M code.

| ``` library MyFunctions {    doubleValue = (x) => x * 2,    sumValues = (x, y) => x + y,    squareValue = (x) => x * x,    concatenateStrings = (x, y) => Text.Combine({x, y}, " "),    isEven = (x) => Number.Mod(x, 2) = 0} ``` |
| --- |

 

Now, let's look at advanced data transformations.

 

### Advanced Data Transformations

Power Query M supports advanced data transformation techniques, including grouping, aggregation, pivoting, unpivoting, merging, appending tables, and handling errors. These features allow effective shaping and cleaning of data from various sources. Here is an example of advanced data transformation:

**Table.Group(): **Groups table rows by the key columns defined by key, which can be a single column name or a list of names. A record is created for each group with the key columns and any specified aggregated columns. 

| ``` let    grouped = Table.Group(    Table.FromRecords({        [CustomerID = 1, price = 20],        [CustomerID = 2, price = 10],        [CustomerID = 2, price = 20],        [CustomerID = 1, price = 10],        [CustomerID = 3, price = 20],        [CustomerID = 3, price = 5]    }), // Records    "CustomerID",    {"total", each List.Sum([price])} // aggregated columns)in    grouped ``` |
| --- |

 

Here is the result:

![Result of advanced data transformations in Power BI](https://blog.dataddo.com/hs-fs/hubfs/M%20code%205.png?width=1359&height=323&name=M%20code%205.png)

 

### Query Dependencies

Power Query M functions can reference and use the output of other queries. This allows you to create a data transformation workflow in which queries build on the results of previous ones. 

| ``` let    previousQuery = SomeOtherQuery,    filteredResult = Table.SelectRows(previousQuery, each [Column1] > 10)in    filteredResult ``` |
| --- |

 

Here, **previousQuery **is the dependent query used in the filtered result.

 

### Query Folding 

Query folding optimizes resource usage, such as CPU and memory, by minimizing data processing within Power BI. It improves performance by pushing data transformations back to the data source, reducing data transfer and the amount of data processed in Power BI. This can be achieved in two ways:

#### Query Folding with SQL Database

Power Query M functions can incorporate SQL statements directly within queries, allowing the database server to handle heavy processing and improve query performance.

| ``` let   Source = Sql.Database("server", "database"),   Query = "SELECT * FROM Restaurants",   Result = Sql.Database(Source, Query)in   Result ``` |
| --- |

 

First, connect to the source and query the database. The query will run on the server, and the result will be fetched.

#### Query Folding with Filtered Rows

Power Query M functions support filter operations that can be pushed back to the data source. For instance, the data source can execute filtering rows based on a condition for more efficient data retrieval. 

The following code extracts the contents of an Excel file and filters the rows where the value in the second column is 'Germany'.

| ``` let    Source = Excel.Workbook(File.Contents("..."), null, true),    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],    FilteredRows = Table.SelectRows(Sheet1_Sheet, each [Column2] = "Germany")    in    FilteredRows ``` |
| --- |

 

And that's M code in Power BI!

 

## Why Use Dataddo to Get Your Data to Power BI?

Dataddo is a no-code data integration platform that can [automate the flow of data from any SaaS app or database to Power BI](https://dataddo.com/app/power_bi).

### Record Time to Insights

Easily connect services to Dataddo via a streamlined interface. Sync data as frequently as every 5 minutes. Then focus purely on deriving profitable insights from your data in Power BI.

### No More Broken Dashboards

Dataddo's engineers proactively monitor and maintain all data connections, so that you never have to wake up to a broken Power BI dashboard in the morning.

### Store Your Historical Data

You don't need to manage a dedicated database in order to collect and analyze data from periods past. You can use Dataddo's embedded SmartCache storage.

### Comprehensive Data Quality Monitoring Tools

Filter, monitor, blend, and transform your data automatically in Dataddo, so that you can spend less time preparing it in Power BI for analysis.

![Dataddo platform: flow screen](https://blog.dataddo.com/hs-fs/hubfs/M%20code%206.png?width=1812&height=776&name=M%20code%206.png)*Dataddo's flow screen. Notice the red boxes—inside are the numbers of data sources combined in a single connection to Power BI.*

All you have to do to get your data flowing is [sign up for a free Dataddo account](https://www.dataddo.com/signup?hsLang=en), then go to [Dataddo Academy](https://academy.dataddo.com/?hsLang=en) for simple video tutorials on how to set up your data connections.

 

## Additional Power BI Tips, Tricks, and Resources

Continue reading the Dataddo blog for more Power BI tips, tricks, and resources!

1. [Mastering DAX Functions in Power BI](https://blog.dataddo.com/mastering-dax-functions-in-power-bi?hsLang=en)
2. [Guide to Slicers in Power BI: Create Better-Looking Reports](https://blog.dataddo.com/guide-to-slicers-in-power-bi-create-better-looking-reports?hsLang=en)
3. [Smarter Financial Dashboards for SMEs (with Free Power BI Template)](https://blog.dataddo.com/smarter-financial-dashboards-for-smes-with-free-power-bi-template?hsLang=en)
4. [Overcome Data Blending Limitations of Tableau, Looker, Power BI](https://blog.dataddo.com/overcome-data-blending-limitations-of-tableau-looker-powerbi?hsLang=en)
5. [How to Connect NetSuite to Power BI: ODBC vs. Dataddo](https://blog.dataddo.com/how-to-connect-netsuite-to-power-bi-odbc-vs-dataddo?hsLang=en)
6. [How to Connect Google Sheets with Power BI: Direct vs. Dataddo](https://blog.dataddo.com/how-to-connect-google-sheets-with-power-bi-direct-vs.-dataddo?hsLang=en)
7. [How to Connect Sage Accounting to Power BI for FREE: Direct vs. Dataddo](https://blog.dataddo.com/how-to-connect-sage-accounting-to-power-bi-in-dataddo?hsLang=en)
8. [How to Connect Google Analytics to Power BI for FREE: Direct vs. Dataddo](https://blog.dataddo.com/how-to-connect-google-analytics-to-power-bi-direct-vs.-dataddo?hsLang=en)
9. [How to connect JSON with Power BI: Direct vs. Dataddo](https://blog.dataddo.com/how-to-connect-json-with-power-bi-direct-vs.-dataddo?hsLang=en)
10. [How to Connect SEMrush to Power BI for FREE](https://blog.dataddo.com/how-to-connect-semrush-to-power-bi?hsLang=en)
11. [Connecting Xero to Power BI: Native or Third-Party Connector?](https://blog.dataddo.com/connecting-xero-to-power-bi-native-or-third-party?hsLang=en)
12. [How to Integrate Exact Online Data with Power BI (or any BI Tool)](https://blog.dataddo.com/how-to-integrate-exact-online-data-with-your-dashboarding-apps?hsLang=en)
13. [Power BI vs Tableau: The Breakdown](https://blog.dataddo.com/power-bi-vs-tableau?hsLang=en)

 

|   Connect All Your Data with Dataddo Just a few quick steps to get your data to Power BI. [![Start for Free](https://no-cache.hubspot.com/cta/default/7885580/7ed5acf0-8674-4253-bada-90465ddc6f8a.png)](https://cta-redirect.hubspot.com/cta/redirect/7885580/7ed5acf0-8674-4253-bada-90465ddc6f8a) |
| --- |

 Category: [tips-tricks](https://blog.dataddo.com/tag/tips-tricks)

### Related Articles**

<https://blog.dataddo.com/the-dataddo-community-forum-a-place-for-support-feedback-resources?hsLang=en>

## [The Dataddo Community Forum: A Place for Support, Feedback, Resources](https://blog.dataddo.com/the-dataddo-community-forum-a-place-for-support-feedback-resources?hsLang=en)

*(15 min read )*

 Topics: [tips-tricks](https://blog.dataddo.com/tag/tips-tricks)

<https://blog.dataddo.com/easy-kafka-data-pipelines-to-databases-and-apps-real-time-streaming?hsLang=en>

## [Easy Kafka Data Pipelines to Databases and Apps: Real-Time Streaming](https://blog.dataddo.com/easy-kafka-data-pipelines-to-databases-and-apps-real-time-streaming?hsLang=en)

*(15 min read )*

 Topics: [tips-tricks](https://blog.dataddo.com/tag/tips-tricks)

<https://blog.dataddo.com/how-to-export-data-from-the-hubspot-api-how-to-import-data-to-hubspot?hsLang=en>

## [How to Export Data from the HubSpot API, How to Import Data to HubSpot](https://blog.dataddo.com/how-to-export-data-from-the-hubspot-api-how-to-import-data-to-hubspot?hsLang=en)

*(15 min read )*

 Topics: [tips-tricks](https://blog.dataddo.com/tag/tips-tricks)

### Subscribe to The Chronicles of Marketing Newsletter

## Comments

## Subscribe to Our Blog

Stay up to date with the latest marketing, sales, and service tips and news.

![subscribe](https://blog.dataddo.com/hubfs/Illustrations/subscribe.svg)

- [****](https://www.linkedin.com/company/dataddo)

- ©  Dataddo, Inc. All Rights Reserved

```json
{
  "@context" : "https://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Haziqa Sajid",
    "url" : "https://blog.dataddo.com/author/haziqa-sajid"
  },
  "dateModified" : "2024-06-25T13:19:41.928Z",
  "datePublished" : "2024-06-13T08:12:09.000Z",
  "headline" : "Guide To M Code in Power BI: A Power Query Formula Language",
  "image" : [ "https://blog.dataddo.com/hubfs/M%20code%20PBI%20preview%20image.png" ],
  "mainEntityOfPage" : {
    "@id" : "https://blog.dataddo.com/guide-to-m-code-in-power-bi-a-power-query-formula-language",
    "@type" : "WebPage"
  },
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "url" : "https://blog.dataddo.com/hubfs/Dark%20logo@3x-1.png"
    },
    "name" : "Dataddo, Inc."
  }
}
```