How to Get Started with M Language and Power BI

t M language is Microsoft's proprietary programming language for Power BI. As adoption of Power BI grows, learning M language is becoming an increasingly valuable skill for data analysts and BI professionals. This guide provides an introduction to M language training, including an overview of the language, how to set up the development environment, basic syntax and concepts, and resources for further learning. Read on to get started with unlocking the full potential of Power BI through M language. This resource is a support tool to be used in conjunction with our Power BI Courses the perfect opportunity to start your teams training in Power BI. To find out more visit our Power BI training course page. 

What is M Language?

M language, originally known as Power Query M Formula Language, is the underlying programming language used in Power BI for data transformations and shaping. Introduced in 2013, M provides users with more advanced functionality compared to Power Query alone.

With M language, analysts can cleanse, transform, integrate, and model data to optimize it for building reports and visualizations in Power BI. Key capabilities offered by M include:

• Importing data from various sources like CSVs, databases, and web APIs
• Shaping and cleansing untidy datasets
• Merging disparate sources into unified views
• Applying data transformations such as pivoting, filtering, and parameterizing
• Creating reusable modules, functions, and custom data connectors

Learning M unlocks the full flexibility of Power BI. Analysts can implement complex data handling logic and optimizations that go beyond the standard graphical interface. Fluent M programmers can build custom visuals, integrate external languages like R and Python, and create enterprise-grade BI solutions.

Example of M language code for transforming and shaping data in Power BI

Setting Up the M Language Development Environment

To start working with M language, you will need to install Power BI Desktop from Microsoft. This free desktop application includes an integrated M language editor for authoring scripts.

Download and install the latest version of Power BI Desktop for your operating system from here.

Once setup is complete, you can enable M language scripting in Power BI Desktop by following these steps:

1. Launch Power BI Desktop and open an existing report or create a new blank report.
2. On the ribbon, go to the View tab.
3. Click on Modeling > New Script.

This will open the M language editor window docked within Power BI Desktop.

Enabling the M language editor in Power BI Desktop

You can also install a dedicated M language IDE like Visual Studio Code for advanced authoring features like debugging and Git integration. Popular lightweight text editors like Atom or Sublime Text also work well.

Writing Your First M Script

With the development environment setup, you can start writing your first lines of M code. Let's go through a simple script that prints the text "Hello World!" to the console output:

// Print hello world "Hello World!" 

Breaking this down:

• // denotes a code comment in M
• "Hello World!" is a string literal, delimited with double quotes
• Printing a value in M automatically outputs it

Let's enhance this script to use variables and take input:

// Store input in a variable name = "Susan"; // Print output using the variable "Hello" & " " & name & "!" 

New concepts used:

• name is a variable storing the input name
• & concatenates strings together
• Variables like name are automatically printed

With just these basic concepts, you can start building real programs in M language for Power BI.

Working with Datasets in M

Being designed for data transformation, M language contains extensive capabilities for loading, shaping, and cleansing datasets in Power BI.

For example, we can import data from a local CSV file:

// Load data from CSV salesData = Csv.Document(File.Contents("sales.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]); // View the first 3 rows salesData{0..2} 

Key operations demonstrated:

• Csv.Document loads from a CSV document
• File.Contents imports the file contents
• salesData{0..2} prints the first 3 rows

We can query, filter, and aggregate this data using M language:

// Group by country and sum the sales salesByCountry = Table.Group(salesData, {"Country"}, {{"Sales", each List.Sum([Sales]), type number}}) // Filter for countries with sales over 200,000 largeCountries = Table.SelectRows(salesByCountry, each [Sales] > 200000) 

This illustrates:

• Table.Group to group and aggregate
• List.Sum to sum a column
• Table.SelectRows to filter rows

M has over 500 data transformation functions for shaping, splitting, combining, and cleaning data.

Visualizing Data with M Language

You can create Power BI visuals directly using M language through the JavaScript API.

First, ensure you enable the API in File > Options and Settings > Options > Preview Features.

Then you can create a bar chart with random data as follows:

// Generate random data data = Table.FromColumns({List.Random(50, type number)}, {"Sales"}); // Create bar chart visual visual = visuals.create("barChart") visual.dataBind(data) visual.title("Sales by Product", visuals.TitleCategory.TitleOnly) visual.axisXTitle("Product") visual.axisYTitle("Sales ($)") 

The key steps are:

• visuals.create instantiates a visual type
• visual.dataBind connects data
• Other visual methods customize properties

This allows complete customization and creation of any visual type with M programming.

Bar chart generated through M language visual creation

Building Reusable M Language Functions

A key part of mastering M language is learning how to author reusable, parameterized functions.

Here is an example function to filter datatable rows by a condition:

FilterRows = (data as table, condition as function) as table => Table.SelectRows(data, condition) 

Breakdown of the syntax:

• FilterRows is the function name
• Input parameters defined using as clauses
• Body performs filter using input data and condition
• as table defines return type

We can call FilterRows like so:

// Filter for sales over 200 result = FilterRows(salesData, each [Sales] > 200) 

Parameterized functions abstract away logic into reusable packages. They form the building blocks of robust M programs.

Creating Custom Visuals with M

Using the JavaScript API, custom visuals can be developed from scratch with M language.

For example, a scatter plot visual that accepts category and value columns as inputs:

// Function to create scatter visual ScatterPlot = (data as table, category as text, value as number) as any => let // Extract category and value columns source = Table.SelectColumns(data, {category, value}), // Create scatter chart visualization vis = visuals.create("scatterChart") vis.dataBind(source) vis.category(category) vis.value(value) vis.title("Scatter Plot") in vis 

Call the function by passing data and field names:

// Generate sample data table = #table({"Product", "Sales"}, {{"Bike", 100}, {"Car", 250}, {"Train", 75}}) // Create scatter plot ScatterPlot(table, "Product", "Sales") 

This approach can build entire libraries of reusable, parameterized visuals.

Building Reports and Dashboards with M

M language can generate entire reports and dashboards in code.

For example, we can programmatically build a sales KPI dashboard:

let // Import sales data source = Csv.Document(File.Contents("sales.csv")), // Format as table salesTable = Table.FromColumns(source, Table.ColumnNames(source)), // Build KPI visuals revenue = visuals.create("kpi") .dataBind(salesTable), .valueName("Revenue") .value(List.Sum([Sales])), profit = visuals.create("kpi") .dataBind(salesTable), .valueName("Profit") .value(List.Sum([Profit])), // Build dashboard container dashboard = visuals.create("dashboard") .addPage(revenue, "Revenue KPI") .addPage(profit, "Profit KPI") in dashboard 

Through a series of M language transformations, visualizations, and containers, full-fledged reports can be generated.

Collaborating and Sharing M Language Assets

M language scripts, functions, and visuals can be packaged and shared for collaboration.

For example, custom visuals can be distributed and imported:

// Load visual from PBIVIZ file MyVisual = Visual.Import("MyCustomVisual.pbiviz"); 

Reusable M functions can be saved as *.m files and referenced:

// Load M code module MyFunctions = Module.FromFile("MyFunctions.m"); // Call function in module Table.AddColumn(MyData, "Tax", each MyFunctions.CalculateTax([Price], 0.2)) 

Analysts can build up repositories of reusable M artifacts to share across teams and projects.

Securing Data and Content with M Language

M language has security capabilities like row-level security (RLS) to restrict data access.

For example, we can define an RLS rule that filters sales data by regional sales manager:

// Function for RLS role filter SalesFilter = (userInfo as table) => let managerRegion = userInfo{0}[ManagerRegion], filteredTable = Table.SelectRows(salesData, each [Region] = managerRegion) in filteredTable // Configure RLS on the Sales table Table.ConfigureRoleRules(salesData, "Sales Manager", SalesFilter) 

Now filters will apply automatically when content is accessed by different user roles.

Dynamic data masking can also be implemented to obfuscate sensitive fields like credit card numbers.

Monitoring Usage and Performance of M

With M code powering key artifacts in production environments, monitoring its usage and performance is critical.

The following snippet logs query events and times to a table:

// Setup query log table logTable = #table({"Query", "Duration"}, {{"Sales Query", 0.25}}), // Register query event handler QueryEvents.QueryCompleted = (queryInfo) => logTable = Table.AddRow(logTable, {queryInfo[Query], queryInfo[Duration]}) 

We can then visualize log data to analyze bottlenecks and optimize slow M scripts.

Advanced profiling of M code can be done using third-party tools like Colectica's M Query Profiler.

Integrating M Language with Other Services

M integrates seamlessly with other Microsoft cloud services like Power Automate and Azure tools.

For example, Power Automate flows can be triggered on Power BI dataset refreshes through M:

// API endpoint for flow apiUrl = "https://prod-xyz.eastus.logic.azure.com:443/...", // Post event on dataset refresh OnPostRefresh = (dataSetName as text) => Web.Contents(apiUrl, [Content=Text.ToBinary(dataSetName)]) 

This allows refreshing flows and downstream processes automatically after upstream M data transforms.

M can also connect to Azure services using built-in connectors. The Azure Data Lake connector, for instance, enables querying and importing Azure Data Lake Store files.

Useful M Language Resources

To further build M language skills, Microsoft Learn provides various free training modules including introductory, intermediate, and advanced content.

The official M language reference documentation covers syntax, functions, and operators in detail.

For community support, the dedicated M language forum on Power BI is quite active. The Microsoft tech community also provides help threads.

Numerous M language code samples are available in GitHub repositories like Microsoft's M Samples.

By leveraging these resources, Power BI users can gain expertise in M language to fully harness its capabilities.

Next Steps in Learning M Language

This guide provided an overview of how to get started with M language training by:

• Setting up a development environment
• Learning core syntax and concepts
• Working with data transformation
• Building visualizations and reports
• Authoring reusable modules
• Deploying and monitoring solutions

To take your skills to an advanced level, some recommended next steps are:

• Developing custom visuals and mashup functions
• Optimizing performance-intensive code
• Building enterprise-scale M architectures
• Integrating
• If you enjoyed this article you might like to look at the following resource guide How to Get Started with M Language Training: A Beginner's Guide to Learning M
•  You can self-study M Language through Microsoft's documentation, community forums, and free online resources. However, instructor-led training provides structure and guidance that accelerates learning. But we would always recommend starting off with one of our courses at JBI Training 
  • Power BI - Power Query & M
  • Power BI - Beyond the Basics
  • Microsoft Business Intelligence 2016
  • Power BI