Converting Static Queries to Dynamic Functions

In addition to the standard library of functions available to M queries, user-defined functions can be created to encapsulate the logic of queries for dynamic applications against parameter inputs. Like SQL-stored procedures, M functions can be created with or without input parameters, which can be required or optional. Additionally, as functions are values in the M language, just like table and list values, they can be invoked on demand and in multiple areas within a given Power BI semantic model.

In this recipe, a function is created to support the integration of a list of employee IDs maintained outside the data warehouse environment. The function accepts the employee ID values as parameter inputs and retrieves related column values.

Getting ready

To prepare for this recipe, follow these steps:

1. Download Employees.xlsx and Converting static queries to dynamics functions Start.pbix from the following GitHub repository...