Module 1 — Prepare the Data

Before you transform or model anything, you need to decide where Power BI will read the data from and how that data will be accessed at report time. Storage mode affects performance, freshness, refresh behavior, source-system load, and which Power BI features are available.

In Import mode, Power BI copies data into the semantic model and stores it in the VertiPaq engine. Report visuals query this in-memory model rather than repeatedly querying the source, so interaction is usually very fast. The trade-off is that source changes are not visible until the semantic model is refreshed. Import is therefore a strong default when the data volume fits comfortably in the model and the required freshness can be achieved with scheduled or on-demand refresh.

In DirectQuery mode, the detailed data remains in the external source. When a user interacts with a report, Power BI generates queries that are sent to that source. This can reduce the amount of data stored in Power BI and can expose more recent source data, but report performance now depends heavily on source performance, network latency, query design, relationships, and the number of visuals on a page.

DirectLake is designed for Microsoft Fabric data stored in OneLake. It allows a semantic model to query lake data without using the traditional Import refresh process or behaving exactly like DirectQuery. It is important to recognize DirectLake as its own storage mode rather than treating it as another name for DirectQuery.

Connections, credentials and shared semantic models

• A shared semantic model lets report authors reuse an existing governed model, including relationships, measures and security, instead of rebuilding the same logic in another PBIX file. A report built this way is often called a thin report.
• Credentials prove that Power BI is allowed to access a source. A gateway provides the network path between the Power BI service and supported on-premises sources. A gateway can be online while the stored credentials are still invalid.
• Privacy levels—Public, Organizational and Private—help Power Query control how data from different sources can be combined. Privacy level is about source isolation during data preparation; it is not row-level security.
• Data source settings are where you commonly change credentials, privacy settings, server/database details and related connection configuration.

Data profiling is the process of examining a dataset before modeling it so that you understand its quality, shape, data types, distributions, missing values and potential key problems. Power BI can load technically valid data that is analytically wrong, so profiling is how you catch issues before they become misleading visuals or broken relationships.

In Power Query, Column quality shows the proportion of valid, error and empty values. Column distribution helps you understand distinct and unique values, while Column profile provides more detailed statistics such as minimum, maximum, average and value distribution. These tools are particularly useful when checking candidate relationship keys, suspicious numerical fields and columns with unexpected blanks.

Power Query profiling can initially be based on the top 1,000 rows. That is useful for quick exploration, but it is not enough evidence to conclude that a column is unique or error-free across a large dataset. When the conclusion matters, switch profiling to the entire dataset.

Data types and errors

• Choose a data type based on the meaning of the field, not only how it looks. Customer IDs, account numbers and postal codes may contain only digits but are usually identifiers, so text can be more appropriate than a numeric type.
• Use Change Type using Locale when a text value such as 01/02/2026 could mean different dates depending on regional conventions.
• Remove Errors deletes rows that contain errors in the selected column. Use it only when losing those rows is acceptable.
• Replace Errors keeps the row and substitutes another value, which is more appropriate when the record must remain in the dataset.
• null, an empty string, zero and an error are different states and should not be treated as interchangeable.

Power Query is Power BI's data preparation layer. Its job is to turn source data into a clean, analysis-ready tabular structure before the data enters the semantic model. Most transformations you perform through the Power Query interface are translated into steps written in the Power Query M language.

Common transformations include splitting or extracting text, replacing values, filling values down or up, changing data types, creating conditional or custom columns, grouping rows, removing duplicates, promoting headers and adding an index. The important exam skill is not simply recognizing the command name; it is knowing how the transformation changes the shape or grain of the data.

Pivot, unpivot and transpose

• Pivot takes values that currently appear down rows and turns them into separate columns. It normally requires an aggregation if more than one source row maps to the same output cell.
• Unpivot converts multiple columns into attribute/value rows. This is especially useful when data arrives in a wide format such as one column for every month.
• Unpivot Other Columns is often more maintainable than selecting a fixed set of month columns. You identify the columns that should remain fixed, and Power Query unpivots all other columns—including future period columns that may appear later.
• Transpose swaps the entire table's rows and columns. It is not the same operation as Pivot.

Power Query can also turn semi-structured data—such as JSON records, lists or XML—into tables by expanding nested objects until the required fields are represented as ordinary columns.

Understanding M and each

M is a functional language. The keyword each is shorthand for creating a function whose current row is represented internally by _. In a row expression such as each [Quantity] * [UnitPrice], the column references are evaluated for the current row.

Merge and Append both combine queries, but they solve fundamentally different problems. Append increases the number of rows; Merge increases the number of columns by matching keys. This distinction is one of the most common PL-300 question patterns.

Use Append when two or more tables represent the same type of entity or event and have compatible schemas—for example, January transactions and February transactions. The rows are stacked into one longer table. Column names and data types should be standardized so that values land in the correct output columns.

Use Merge when one query needs attributes from another query. A merge works like a database join: Power Query compares one or more key columns and returns rows according to the selected join type. After the merge, the matching table appears as a nested column that you expand to select the fields you need.

Join keys should represent the same business meaning and use compatible data types. Also remember that duplicate keys can multiply rows during a merge. If one customer key appears twice on the left and three times on the right, matching that key can produce six combinations.

Combining files

When a folder receives recurring files with the same structure, use the Folder connector and Combine Files pattern. Power Query creates a sample-file transformation and applies the same logic to each file. This is much more scalable than importing every monthly file manually.

Query folding means Power Query translates supported transformation steps into a query that the source system can execute. Instead of retrieving a large raw table and transforming every row locally, Power BI asks the source to do as much of the work as possible.

For a relational database, a filter in Power Query might be translated into a SQL WHERE clause and a column-selection step into a SQL SELECT list. This can dramatically reduce the amount of data transferred across the network and the work performed by the Power BI refresh process.

Why step order matters

Transformations such as filtering rows, selecting columns and some joins can often fold. Other operations may not be translatable for a particular connector. Once a non-foldable step occurs, later steps may also need to execute locally. Therefore, place highly selective, foldable operations—especially row filters and removal of unnecessary columns—as early as is logically safe.

• Use query folding indicators or View Native Query where the connector supports them to determine whether steps are being pushed to the source.
• Do not assume every operation folds for every connector. Folding capability depends on the source and the transformation.
• Query folding matters in Import mode because it can improve refresh efficiency; it is not only a DirectQuery topic.
• Incremental refresh is particularly effective when the date-range filter can fold to the source.

Parameters and load settings make Power Query solutions reusable and controllable. They are especially important when the same logic must work across environments or when a large table should refresh only its recent partitions instead of reloading its entire history.

A Power Query parameter is a named value that can be referenced by query steps—for example, a server name, folder path, minimum date or environment name. Changing the parameter changes the behavior of the query without rewriting each step that uses the value. Power Query parameters are different from What-if parameters, which are model features used for interactive report scenarios.

Incremental refresh

Incremental refresh partitions a large table by date so that Power BI can retain historical data while refreshing only a recent period. The standard configuration uses two Date/Time parameters named RangeStart and RangeEnd. The query filters the source date column so each partition has a non-overlapping boundary.

• A common filter pattern is Date >= RangeStart and Date < RangeEnd. Using a strict upper boundary prevents the same row from belonging to adjacent partitions.
• The store period defines how much historical data is retained. The refresh period defines how much of the recent history is reprocessed on each refresh.
• Detect data changes can use a modification timestamp such as LastModifiedDate to determine whether an older partition needs to be refreshed.
• For supported sources, the range filter should fold so that the source returns only the rows required for the partition.

Load settings and query reuse

Enable load controls whether a query becomes a table in the semantic model. A staging query can have Enable load turned off while downstream reference queries still use its result. Include in report refresh is a separate setting that determines whether the query participates in refresh.

A Reference query starts from another query's result, so future upstream transformations flow into the reference. A Duplicate copies the current steps and then becomes independent. Use Reference when you want a reusable staging pipeline; use Duplicate when you intentionally need a separate copy of the logic.