This chapter follows up on the dataset planning process described in the previous chapter by implementing M queries in a new Power BI Desktop file to retrieve the required fact and dimension tables. Parameters and variables are used to access a set of SQL views reflecting the data warehouse tables inside a SQL Server database and the Annual Sales Plan data contained in an Excel workbook. Additional M queries are developed to support relationships between the sales plan and dimension tables and to promote greater usability and manageability of the dataset.
Three examples of implementing data transformations and logic within M queries, such as the creation of a dynamic customer history segment column, are included. Finally, tools for editing and managing M queries, such as extensions for Visual Studio and Visual Studio Code, are reviewed.
In this chapter, we will review the following topics:
- Query design per dataset mode
- Data sources
- Power BI Desktop options
- SQL views
- Parameters
- Staging queries
- Data types
- Query folding
- M query examples
- M query editing tools