Dynamic M query parameters in Power BI with DirectQuery source queries

Filter DirectQuery table from slicer selection.

Previously it was not possible to dynamically filter M queries from the Power BI report view.

With the latest Power BI version updates of February, it is now possible to dynamically filter a SQL Server data source using Power Query M parameters and slicer visual.

We need to bind the specific column in the data source with the Power Query parameters to dynamically filter it.

==>It is a really amazing feature as from the Power BI report, we can decide how many rows to load into the dataset without getting into the Power Query Editor!

Here are the steps I went through to filter my SQL table dynamically from slicers:

1) I created a SQL function with two inputs (filter my Sales tables based on customer_key and product_key entries)

2)I called the function in Power BI with DirectQuery mode.

3)I created two Power Query M parameters :Product_key, customer_key, to be passed to the function

4)In Power Query Editor, I modified the query calling the function to pass it the M parameters

4)I created the two columns product_key and customer_key which will be used in the slicers.

5)In the Power Bi Modelling view, I bound the columns to the parameters

==> My DirectQuery table will be dynamically filtered from the slicers selection.