Blog

You are currently viewing Advance M Language concepts in PowerBI

Advance M Language concepts in PowerBI

Loading

In this blog, you will learn about the advance M Language concepts in PowerBI such as how to create custom functions in PowerBI, error debugging, recursive functions, Query parameters and dynamic data sources in PowerBI.

Custom Functions

Custom functions in Power BI allow you to encapsulate reusable logic. These are the following steps to Create a Custom Function:

Step 1. Start Power BI Desktop.

Step 2. Go to Home tab, select Get Data and choosing your data source.

A screenshot of a computer

Step 3. Click Load to import data in power bi.

Step 4. Go to Home tab and Click Transform Data to open the Power Query Editor.

Step 5. Go to Home tab and New Source to choose Blank Query.

= let
      Source = (x as number, y as number) as number =>
      let
          result = x + y
      in
          result
  in
      Source

Here, this function named Source, takes two numbers and returns their sum.

Invoke the Function:

Step 6. Create another blank query.

In the formula bar, call the function with specific arguments:

= let
      result = Source(3, 5)
  in
      result

Error Management and Debugging

Error management in M ​​language helps to effectively manage and troubleshoot errors. These are the following Steps for Error Management

Using try expression: The try keyword attempts to evaluate an expression and catches any errors. 

let
    Source = try (1 / 0)
in
    if Source[HasError] then "Error encountered" else Source[Value]

Debugging Techniques:

  • Preview data: Check intermediate results using the preview window.
  • Step by step evaluation: Add intermediate steps to isolate where the error occurs.
  • Error message: Use try to get detailed error message. Example
= let
  Source = try (1 / 0)
  in
  if Source[HasError] then Source[Error][Message] else Source[Value]

Recursive functions

Recursive functions call themselves to perform repetitive tasks.

Create a recursive function:

  • Define the recursive function.
  • Example: Calculate the factorial of a number.
= let
      factorial = (n as number) as number =>
      if n = 0 then 1 else n * @factorial(n - 1)
  in
      factorial

Apply the recursive function:

  • Create a new query to call the recursive function.
let

result = Factorial(5)

in

result

Query parameters and dynamic data sources

Query parameters allow dynamic changes to a data source query. These are the following Steps to use query parameters:

Step 1. Go to Home tab; Click on Manage Parameters and select New Parameter.

Step 2. Define parameters, such as the start date and end date for the date range.

Parameter: startDate

Parameter: endDate

Step 3. Reference parameters in your queries:

= let

      StartDate = #"startDate",

      EndDate = #"endDate",

      FormattedStartDate = DateTime.ToText(StartDate, "yyyy-MM-dd HH:mm:ss"),

      FormattedEndDate = DateTime.ToText(EndDate, "yyyy-MM-dd HH:mm:ss"),

      Source = MySQL.Database("127.0.0.1", "data", [Query = "SELECT * FROM sales_data WHERE order_date >= '" & FormattedStartDate & "' AND order_date <= '" & FormattedEndDate & "'"])

  in

      Source

Step 4. Use parameters to dynamically change the data source: 

let

ServerName = "YourServerName",

DatabaseName = "YourDatabaseName",

TableName = "YourTableName",

Source = Sql.Database(ServerName, DatabaseName, [Query = "SELECT * FROM " & TableName])

in Source

Conclusion

In Power BI Desktop, advanced M language concepts such as custom functions, error handling, recursive functions, and query parameters enable powerful data transformation capabilities. By above detailed steps, you can use these features to create dynamic and efficient data models, leading to faster and more insightful analysis.

If you like the article and would like to support me, make sure to:

Leave a Reply