Power BI: Create Dynamic Queries Using M Code Parameters

Dynamic queries let you adapt your data transformations on a whim without having to manually edit each step in the transformation that would be affected by the change. This can be done using parameters.

What are Parameters in M Code?

Parameters are variables that hold values like numbers or text. Instead of hard-coding certain values into certain M code functions, you can reference a variable in the function so that you can change the variable to change what the function input is. This makes your data transformation flexible and reusable.

Example: Creating Date Filters

You own a boba tea shop and you need to build a report on your sales dataset however before you build anything off of it you need to filter it to a specific date range.

The first thing you would do is define your parameters.

StartDate = #date(2022, 1, 1)
EndDate = #date(2022, 12, 31)

Then you need to take the steps to get the file, filter the dataset using your parameters, and then load.

let
    Source = Excel.Workbook(File.Contents("Boba Sales.xlsx"), null, True),
    SalesData = Source{[Name="Sales"]}[Content],

    ChangedType = Table.TransformColumnTypes(SalesData, {{"OrderDate", type date}, {"SalesAmount", type number}}),

    FilteredRows = Table.SelectRows(ChangedType, each [OrderDate] >= StartDate and [OrderDate] <= EndDate)

in
    FilteredRows

The Table.SelectRows takes the start and end date parameters from earlier to filter the dataset.

You don’t have to just stick to dates, you can use this to filter many other types of columns.

Some More Tips

• Data types - Make sure that the data type of your parameter is the same as the data type of the value that you intend to use it for. For example, if you want to use parameters to filter by date, your parameters’ data type has to be a date and same goes for the column being filtered.

• Naming your parameters - Make sure that you give your parameters meaningful and clear names for better code readability for when you or someone else looks at the code later. For examples, use StartDate and EndDate rather than a and b.

• Test the query - Check whether the parameters are being used dynamically, try changing them and see if you get the expected change in your transformation.