In this chapter, we've covered all components of the data retrieval process used to support the dataset for this project as described in Chapter 1, Planning Power BI Projects. This includes the layer of SQL views within a database source, source connectivity parameters in Power BI Desktop, and the M queries used to define and load the dimension and fact tables of the dataset. In constructing a data access layer and retrieval process for a dataset, we've also discussed the design considerations relative to import and DirectQuery datasets, Power BI Desktop configuration options, and data source privacy levels. Additionally, we've reviewed core concepts of the M language, including query folding, item access, and data types. Moreover, we've reviewed three examples of efficiently implementing impactful data transformation logic via M queries as well as the tools for developing and editing M queries.

In the next chapter, we'll leverage the M queries and design techniques described in this chapter to create import and DirectQuery data models. Specifically, the dimension and fact table M queries will become the dimension and fact tables of the data model, and relationships will be defined to form multiple star schemas. Additionally, the bridge table M queries will be used to support the analysis of historical sales and margin results versus the annual sales and margin plan.