Learning Objectives
By the end of this chapter, you will be able to:
- Connect to a range of data sources and to shared semantic models, and resolve the common import errors that arise during initial extraction
- Change credentials, authentication kinds, and privacy levels for an existing data source connection
- Select between DirectLake, DirectQuery, and Import storage modes based on workload, latency, and platform constraints
- Create, modify, and bind Power Query parameters to drive flexible, environment-aware refresh logic
- Profile column quality, distribution, and statistics, and convert nested or semi-structured payloads into rectangular tables
- Resolve null values, inconsistent text, and type mismatches, and shape the result into fact and dimension tables suitable for star-schema modeling
- Configure load behavior, query references, and merge/append patterns that minimize refresh cost and keep relationships valid
Executive Summary
- Power Query (the M engine inside Power BI Desktop) is the primary transformation surface; every storage mode except Live Connect routes through it before data lands in the model
- Storage mode is a decision per table: Import gives in-memory speed, DirectQuery gives source-of-truth freshness, DirectLake gives lake-native performance inside Microsoft Fabric, and composite models mix them
- Privacy levels and the Formula Firewall are not security features but evaluation isolators that prevent data from one source being passed into the query of another
- Reference and duplicate queries look identical in the editor but behave very differently at refresh time: a reference inherits upstream steps, a duplicate forks them
- Fact and dimension table design starts in Power Query, not in the model view; the keys, grain, and conformed attributes produced here determine relationship validity downstream
Assumptions
- The reader has installed Power BI Desktop and has experience opening the Power Query Editor and authoring basic queries
- Microsoft Fabric capacity is available when DirectLake examples are referenced; otherwise the equivalent Import or DirectQuery path applies
- Service names follow current Microsoft branding: Microsoft Entra ID for identity, Microsoft Fabric for the unified analytics platform, and "semantic model" for what was previously called a dataset
- Power Query M and DAX are both referenced; the chapter focuses on M because DAX modeling belongs to a later chapter