CUSTOMISED
Expert-led training for your team
Dismiss
Advanced M Language Techniques for Power BI

19 September 2023

Advanced M Language Techniques for Power BI

Advanced M Language Techniques for Power BI

Now that you have a solid grasp of M language basics, it's time to unlock more advanced capabilities. This guide covers techniques like performance optimization, custom visual development, Python and R integration, error handling, and deployment options to take your M skills to the next level. This article is part of JBI Trainings M Language course and is intended as a support resource for our Power BI - Beyond the Basics

Optimizing M Language Performance

As M codebases grow, query performance can degrade. Some optimization techniques include:

  • Using proper data types - tables vs. lists, typed columns, etc.
  • Limiting loaded data with filtering
  • concatenation vs. Table.Combine()
  • Parallelizing with #shared keyword
  • Utilizing query diagnostic tools
// Filter Sales table before loading 
FilteredSales = Table.SelectRows(Sales, each [Country] = "USA")

// Concatenate with Table.Combine()
CombinedSales = Table.Combine({FilteredSales, FilteredReturns}) 

Profiling tools like the Power BI Performance Analyzer and M Query Profiler provide detailed query diagnostics to identify and fix bottlenecks.

Developing Custom Visuals in M

The Power BI custom visual SDK enables building visuals using M language.

// Sample bar chart custom visual
Visual = (data as table, category as text) as visual =>

  Visual.Chart(data,
    Visual.Column(category),
    "BarChart", [IsMultiSelect = true]) 

After developing the visual, package it into a PBIVIZ file using the Power BI Desktop exporter. Then import into reports using Visual.Import

Custom visual in Power BI

Custom visual developed with M language

Integrating R and Python in M

R and Python scripts can be directly embedded within M code.

// Run Python script
Python.Execute(Text.FromBinary(pyScript))

// Convert R dataframe to table  
mTable = R.DataFrameToTable(rDataframe)

This enables reusing R and Python libraries while keeping M as the top-level orchestration language.

Handling Errors in M

Robust error handling prevents failures in production.

Try GetData = Source(){1},
  Otherwise Error = Exception.Message, 
  "Error getting data: " & Error 

Techniques like wrapping code in try/otherwise blocks and logging detailed errors prevent query failures.

Deploying M Solutions

To scale out M usage, solutions can be deployed in Azure using:

  • Power BI Premium - For shared capacity
  • Azure Analysis Services - For enterprise BI models
  • Power Apps - For commenting to other services

With proper deployment governance, M can power organization-wide analytics solutions.

Conclusion

This guide presented more advanced techniques to elevate your M language skills. Focus on performance tuning, robust error handling, custom visualization, and enterprise deployment to take your M programming to the next level.

Recommended courses at JBI Training 

CONTACT
+44 (0)20 8446 7555

[email protected]

SHARE

 

Copyright © 2024 JBI Training. All Rights Reserved.
JB International Training Ltd  -  Company Registration Number: 08458005
Registered Address: Wohl Enterprise Hub, 2B Redbourne Avenue, London, N3 2BS

Modern Slavery Statement & Corporate Policies | Terms & Conditions | Contact Us

POPULAR

Rust training course                                                                          React training course

Threat modelling training course   Python for data analysts training course

Power BI training course                                   Machine Learning training course

Spring Boot Microservices training course              Terraform training course

Kubernetes training course                                                            C++ training course

Power Automate training course                               Clean Code training course