Converting static queries into dynamic functions
In addition to the library of functions available to M queries, user defined functions can be created to encapsulate the logic of queries for dynamic application against parameter inputs. Similar to SQL stored procedures, M functions can be created with or without input parameters and these parameters 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 data 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.
How to do it...
In this example, a business team a list of employee IDs in an Excel workbook and wants the ability to access several columns from the employee dimension table in the data model related...
