PL-300: Prepare the Data (15-20%)

Import

Data is cached in the Power BI model. Queries fulfilled by the cached data. Offers high performance but requires scheduled refreshes to update data.

Direct Query

Data is not cached. Visuals query the data source directly in real-time. This is ideal for large datasets or near real-time requirements (latency < 5 mins).

Dual (Composite)

A hybrid mode where tables can act as either cached or uncached. Allows Power BI to choose the most efficient path depending on the query context. Often used for dimension tables related to both Import and DirectQuery fact tables to reduce limited relationships.

Near Real-Time Data (Ex: Sales)

Must use DirectQuery to ensure immediate visibility of updates

Infrequent Refresh (Ex: SalesAggregate/Date)

Should use Import to optimize performance via caching if data updates weekly or less often.

Cross-Mode Relationships (Ex: Customer/Date/Geography)

Use Dual mode for dimension tables that link to both DirectQuery and Import tables to maintain high performance in visuals.

Microsoft Dataverse

The primary connector for data hosted in Microsoft Power Apps or Project for the web within Microsoft Teams

OData Feed

A generic connector used for data sources not identified in standard lists (like Cassandra via ODBC or Dynamics 365).

Web/SharePoint Folder

Required for Excel files stored in OneDrive or SharePoint Online to ensure data can be refreshed in the Power BI Service without an on-premises gateway.

Azure DevOps (Boards only)

Recommended for authenticating and filtering cloud-based Analytics views in Azure DevOps

Merge (Join)

Used when adding more columns from another query based on a matching key (Ex: Customers and Address via Address ID).

Append (Union)

Used when adding more rows from another query with the same structure (e.g., combining two identical Customer tables from different databases).

Inner Join

Returns only matching rows from both tables

Left Outer Join

Maintains all rows from the left table and matching rows from the right.

Unpivoting

Converts columns into attribute-value pairs to flatten data (e.g., converting yearly columns into a single "Year" column for better time-series visualization).     

Transposing

Swaps rows and columns.

Splitting Columns

Separates combined data strings (Ex: splitting a "Logged" field like 2018-12-31 at 08:59 using " at " as a delimiter to isolate Date and Time).

Filling Data

“Fill Down” copies a value into the blank cells below it. “Fill Up” copies a value into the blank cells above it.

Custom Columns

Concatenating values with mixed types (e.g., Text & Number) requires manual type conversion to avoid "Mismatched Data Types" errors

Column Distribution

Shows counts for "Distinct" values (total different values) and "Unique" values (values appearing exactly once).

Column Quality

Displays the percentage of data that is "Valid", "Error", or "Empty".

Column Profile

Shows a summary of the data in a column