Accessing and Retrieving Data

Power BI Desktop contains a rich set of connectors and transformation capabilities that support the integration and enhancement of data from many different sources. These features are all driven by a powerful functional language and query engine, M, which leverages source system resources when possible and can greatly extend the scope and robustness of the data retrieval process beyond what's possible via the standard query editor interface alone. As with almost all BI projects, the design and development of the data access and retrieval process has significant implications for the analytical value, scalability, and sustainability of the overall Power BI solution.

In this chapter, we dive into Power BI Desktop's Get Data experience and walk through the process of establishing and managing data source connections and queries. Examples are provided of using the Power Query Editor interface and the M language directly, to construct and refine queries to meet common data transformation and cleansing needs. In practice and as per the examples, a combination of both tools is recommended to aid the query development process.

A full explanation of the M language and its implementation in Power BI is outside the scope of this book, but additional resources and documentation are included in the sections titled There's more... and See also.

The recipes included in this chapter are as follows:

• Viewing and Analyzing M Functions
• Managing Queries and Data Sources
• Using DirectQuery
• Importing Data
• Applying Multiple Filters
• Selecting and Renaming Columns
• Transforming and Cleansing Source Data
• Creating Custom Columns
• Combining and Merging Queries
• Selecting Column Data Types
• Visualizing the M Library
• Profile Source Data
• Diagnosing Queries