With the SQL views created, the data sources configured, and the Power BI Desktop environment options applied, the dataset designer can finally develop the data retrieval queries and parameters of the dataset.
Within the Power Query Editor of Power BI Desktop, group folders can be used to organize M queries into common categories such as Data Source Parameters, Staging Queries, Fact table Queries, Dimension Table Queries, and Bridge Table Queries as shown in the following screenshot:
The parameters and queries displayed with a gray font are included in the refresh process of the dataset but not loaded to the data modeling layer. For example, the AdWorksSQLServer query displayed in the preceding image merely exposes the objects of the SQL Server database via the Sql.Database() M function for other queries to reference. This query, along with the data source parameters, all have a gray font and are used to streamline the data retrieval process such that a single change can be implemented to update many dependent queries.
Right-click a query or parameter in the queries list to expose the Enable load and Include in report refresh properties as shown in the following screenshot:
For many datasets, the only queries that should be loaded to the data model are the dimension and fact table queries and certain parameter table queries. For this dataset, three bridge tables will also be loaded and included in the report refresh to support the analysis of Internet Sales and Reseller Sales data versus the annual Sales and Margin Plan.