With the data source parameters configured, staging queries can be used to expose the data sources to the dimension and fact table queries of the dataset. For example, the AdWorksSQLServer staging query merely passes the production server and production database parameter values into the Sql.Database() M function as shown in the earlier image of the Power Query Editor interface. This query results in a table containing the schemas and objects stored in the database, including the SQL views supporting the fact and dimension tables.
The SalesPlanFilePath staging query used for the Annual Sales Plan Excel workbook source is very similar in that it merely references the file name and file directory parameters to form a complete file path, as shown in the following screenshot:
The third and final staging query, CurrentDateQry, simply computes the current date as a date value:
Just like parameters, the results of staging queries, such as CurrentDateQry, can be referenced by other queries, such as the filtering condition of a fact table. In the following sample M query, the Table.SelectRows() function is used in the Internet Sales query to only retrieve rows where the Order Date column is less than or equal to the value of the CurrentDateQry (10/16/2017):
let
Source = AdWorksSQLServer,
ISales = Source{[Schema = "BI", Item = "vFact_InternetSales"]}[Data],
CurrentDateFilter = Table.SelectRows(ISales, each [Order Date] <=
CurrentDateQry)
in
CurrentDateFilter
In this simple example, the same filter condition can easily be built into the SQL view (vFact_InternetSales), supporting the fact table and this approach would generally be preferable. However, it's important to note that the M engine is able to convert the final query variable (CurrentDateFilter), including the reference to the staging query (CurrentDateQry), into a single SQL statement via Query Folding. In some data transformation scenarios, particularly with rapid iterations and agile project lifecycles, it can be preferable to at least temporarily utilize efficient M queries within the Power BI dataset (or Analysis Services model) rather than implement modifications to the data source (for example, data warehouse tables or views).