Parameterized Data Source Queries
For Power BI datasets, a recommended data access method is to define parameters specific to the data sources and then develop a query (a staging query) which references those parameters. Other M queries, such as the dimension and fact table queries of the dataset, can then reference this staging query.
For example, the following query named AdWorksSQLServer exposes the objects (views, tables, functions) of a SQL Server database:
Note from the Queries list on the left that the query is not loaded to the data model. This query is only included in the refresh of the dataset and referenced by other queries which retrieve data from the specific SQL Server database referenced by AdWorksSQLServer. The query contains only one function, Sql.Database(), as data source access is its only purpose.
Data Source Parameters
The SQL Server database is determined by values set for the ProdServer and ProdDB parameters. As one approach, you might maintain a short list of valid values for both production and development server and database parameters. When necessary, revising the current value for these parameters will then flow to the data source staging query and then finally to the queries which utilize the given data source (e.g. product, customer, sales).
File Example
Similar to the Sql.Database() example, parameters can be built into a data source query for a file such as the following Excel workbook path:
For this query, we’ve used parameters specific to the directory location and file name for an Excel workbook used as a source. The query returns a single text value (the full file path) that can be used in a separate query to retrieve from the Excel workbook:
let
Workbook = Excel.Workbook(File.Contents(SalesPlanFilePath),true,true),
PlanTbl = Workbook{[Item="PlanTbl",Kind="Table"]}[Data],
DataTypes = Table.TransformColumnTypes(PlanTbl,...........)
in
DataTypes
- Note that the SalesPlanFilePath query is also grayed out like the AdWorksSQLServer query. It’s not loaded to the data model.
SQL View Reference
With the data source staging query already defined for SQL Server, the SQL views created for each required dimension and fact table of the dataset can be easily referenced:
In this example of the Currency dimension, no further M transformations or logic is necessary. The query only retrieves the results of the BI.vDim_Currency view from the SQL Server database defined by the source parameters (ProdServer, ProdDB).
Note: Preferably, any further transformation could be built into the SQL view or further upstream in the data warehouse. However, sometimes that’s not possible, in the short term at least, and further M functions could be added thoughtfully to the query to still leverage the resources of the SQL Server database via Query Folding.
From an M query perspective, we first access a specific record of the table produced by the AdWorksSQLServer staging query. One of the fields of this record is named Data and contains the actual SQL view. As an alternative access method, you can retrieve the record of the staging query via the Name column only and use three variables in the M query per the following code:
let
Source = AdWorksSQLServer,
CurrencyRecord = Source{[Name = "BI.vDim_Currency"]},
CurrencyView = CurrencyRecord[Data]
in
CurrencyView
Summary
The main point of this is to abstract away data source parameters from data retrieval queries and to make it easy to manage data sources (and thus their queries). Of course, there are many other great use cases for query parameters and staging queries (e.g. Power BI Templates (.PBIT) files). Additionally, there are valid use cases for inline queries rather than separate queries – I’ll post an example or two of these sometime soon.
I’ll be talking about the data access layer to Power BI datasets a bit at Boston Code Camp 28 and both of my Power BI books go fairly deep into this topic as well.
Insight Quest 
7 comments