This has bugged me for a long time, and I wasn’t sure how to get around it. I was tasked with creating a Power BI report using data from a reasonably big data set, but the report only displayed a really small part of it. The source is in the neighborhood of 10,000,000 rows, and my report displayed roughly 100,000. Why try to direct query, import, or incrementally refresh all of the data when such a small part of the data is needed? I was looking for a way to filter the data set before you add the applied steps in the query editor. Something like: Select * from Orders where Status = Open

The answer I was looking for is Native Query, and I’m not sure how I completely failed to learn about this until now.

For this example, I’m using the Northwind Traders orders dataverse table. Connect to the dataverse environment, select the orders table, then open the Advanced Editor window. Change the query to this:

let
    Source = CommonDataService.Database("taco.crm.dynamics.com"),
    superQuery = Value.NativeQuery(Source, "Select * from nwind_orders")
in
    superQuery

All I’m doing here is checking to see if the newly formed query is working. Next, right-click on the step where you created the Native Query in the Applied Steps window and select view Native Query. Update the query only to show New orders. This can be done by adding a Where clause:

Select * From nwind_orders Where nwind_orderstatusidname = 'New'

Click Ok, and the query should refresh, only showing new orders.

let
    Source = CommonDataService.Database("taco.crm.dynamics.com"),
    superQuery = Value.NativeQuery(Source, "Select * From nwind_orders Where nwind_orderstatusidname = 'New'")
in
    superQuery

That’s all it takes to filter the data set before transforming it! Be sure to check the linked documentation and take a look at query folding.