Power BI Blog: Composite Models and Dynamic M Query Parameters Part 1

Welcome back to this week’s edition of the Power BI
blog series.  This week, we create Dynamic M Query Parameters and
reference them in an M query. 

Microsoft have
added support recently for Power BI datasets added that have Dynamic M Query Parameters defined.  Now, we may
create a composite model on such datasets to enrich or extend them.  With Dynamic M Query Parameters, we can
let report viewers use filters or slicers to set values for an M query
parameter.

With Dynamic M Query Parameters, model developers can let report viewers use filters or
slicers to set the value(s) for an M Query Parameter, which can be
especially useful for query performance optimisations.  With Dynamic M Query Parameters, model
authors have more control over how filter selections get incorporated into
DirectQuery source queries.

When builders
understand the intended semantics of their filters, they often know how to
write efficient queries against their data source, and can thus ensure filter
selections get incorporated into source queries at the right point to achieve
their intended results with improved performance.

As a prerequisite
for this feature, we must have a valid M Query Parameter created and
referred in one or more Direct Query tables. 

Example

In Power BI
Desktop, we select Home -> Transform data- > Transform data to
open the Power Query Editor.  Then, we select
‘New Parameters’ under the ‘Manage Parameters’ button in the Ribbon.

We fill out the
following information about the parameter:

If we have more
parameters to add, we can simply click ‘New’:

Once we’ve created our
parameters, we can reference them in the M query.  To modify the M query, we open the
Advanced Editor, having selected the query that we want to modify:

Then, reference the
parameters in the M query, highlighted in yellow (below):

Now that we have
created the parameters and referenced them in the M query, we need to create
a table with a column that provides the possible values available for that
parameter.  This will allow it such that
the parameters are dynamically set based on filter selection.  In this example, we want our StartTime parameter
and EndTime parameter to be dynamic.  Since these parameters require a
Date/Time parameter, we want to generate date inputs that may be used to set
the date for the parameter.  

That’s where we
will pick this up next week.

Check back next week for more Power BI tips
and tricks!