M query summary

In summary, the Power Query Editor interface in Power BI Desktop should contain the following types or groups of queries:

• Parameters:
  • These will be used to store individual values essential to the data retrieval that could change, such as the names of servers, databases, and file paths.
• Staging Queries:
  • These queries will not be loaded to the data model but will contain logic used by one or many other queries.
  • For example, a staging query will connect to a specific SQL Server database based on two parameters (server and database) and this staging query will be used by the fact and dimension table queries. 
• Fact and Dimension Queries:
  • These queries will define the tables exposed to the data model layer and optionally the reporting interface. 
  • It's essential that these queries contain columns supporting the relationships of the data model as well as all columns needed for calculations/aggregations, grouping, and filtering in reports. 
• Parameter Tables (optional):
  • Additional tables can be loaded to the data model that don't contain relationships but are used for other purposes, such as the user interface as a placeholder for hidden logic.