PL-300 · CHEAT SHEET

Power BI Data Analyst

Key concepts grouped by official exam weight.

Download as PDF (free, no sign-up)

1. Prepare the data

25–30%

Get or connect to data

Power BI Desktop uses Power Query to connect, and the connector articles list what each source supports, such as DirectQuery. To reuse a published semantic model you use Get data > Power BI semantic models, which creates a live connection: it requires Build permission and does not let you change the model, only add report measures and other calculations.
In Power BI Desktop, File > Options and settings > Data source settings lets you edit permissions: change credentials, clear permissions and set the privacy level, either globally or for the current file only. The levels are Private, Organizational and Public; a Private source cannot fold into other sources. Ignoring privacy levels can expose confidential data.
Import copies data into memory and offers the richest feature set, but it needs refreshing. DirectQuery imports no data and queries the source on every interaction, which suits huge volumes or near real-time needs. Direct Lake, in Fabric, loads Delta tables from OneLake with the VertiPaq engine, and its refresh only copies metadata (framing).
A parameter is created from Manage Parameters or with Convert to Parameter on a query that holds a constant value. It has a name, type, suggested values and a current value. It is used as a step argument or function argument, and incremental refresh requires RangeStart and RangeEnd of Date/Time type. In the service you can edit parameters but not create them.

Profile and clean the data

The Power Query Editor data profiling tools are Column quality, Column distribution and Column profile, enabled from the View tab. By default they analyze the first 1,000 rows, but they can be based on the entire data set. Quality shows valid, error and empty values; distribution reports distinct and unique values.
Cell-level errors do not stop a query from loading and can be removed, replaced with a value, or kept for auditing. Null values are filled with Fill down or Fill up, and Replace values corrects wrong values or turns empty cells into null. try ... otherwise supplies an alternative value when an expression errors.
Import errors are diagnosed from the error reason, message and detail. Common causes: a locale that does not match the source format, types inferred from only the first 200 rows, incorrect Excel sheet dimensions, legacy formats that need the Access Database Engine, and Formula.Firewall errors from privacy levels or steps that mix data sources and query references.

Transform and load the data

The data type is defined at column level and drives the transformations Power Query offers. For unstructured sources (Excel, CSV, text) it detects types and headers by inspecting values; a column with no explicit type stays Any, which you should avoid. Change type using locale interprets dates written in another format correctly. Fixed decimal is always precise, unlike the floating-point decimal number.
New columns are created on the Add column tab: custom with an M formula, conditional with clauses tested from top to bottom plus a final Else clause, or from examples when you know the result but not the transformation. New conditional columns have no data type defined, so you must set one. An index column starts at 0 with an increment of 1 and supports other transformation patterns.
Group by summarizes rows using the values of one or more columns; the Advanced option lets you choose several grouping columns. Operations are column operations (Sum, Average, Median, Min, Max) and row operations (Count rows, Count distinct rows and All rows). All rows returns a nested table per group, on which you can add a custom column, for example with Table.Max, and expand the resulting record.
Pivot turns the values of one column into headers and aggregates the values of another column (Sum by default); with Don't aggregate it expects a single value per intersection and errors if there are several. Unpivot turns columns into Attribute and Value pairs; Unpivot other columns picks up new source columns on refresh. Transpose rotates the table and loses the headers, which Use first row as headers restores.
The JSON connector applies automatic table detection to flatten the data; for several files you use a folder connector. Parse interprets a text column as JSON, giving Record values, or as XML, giving Table values; on the Transform tab it changes the column and on Add column it creates a new one. Expanding a list creates a row per value repeating the other columns, and expanding a record creates a column per field.

2. Model the data

25–30%

Design and implement a data model

Column properties define how Power BI treats each field in visuals. Data category clarifies what a value means (for example, geographic) so it is drawn correctly on a map. Sort by column requires both columns to be at the same level of granularity, and the sort column must hold one unique value for each sorted value, such as month name sorted by month number.
A role-playing dimension, such as order, ship and delivery date, filters the same facts in different ways. Power BI allows only one active relationship between two tables; the others are inactive and are only used through USERELATIONSHIP. To filter by several roles at once, create one table per role, for example a calculated table that copies the original, each with its own active relationship.
Cardinality can be many-to-one (the most common and default), one-to-one, one-to-many or many-to-many, and it requires at least one side to have unique values except in many-to-many. Cross filter direction is single by default; Both treats the tables as one but can create ambiguity and hurt performance, so use it sparingly and prefer CROSSFILTER inside a measure.
A date table has one row per day and a unique date column with no blanks or missing dates, spanning full years. It can be loaded from a warehouse, generated in Power Query, or created with CALENDAR and CALENDARAUTO. Marking it as a date table validates those rules and is required if the relationship uses an integer yyyymmdd key. Auto date/time creates one hidden table per date column.
Calculated columns and tables use DAX over data already loaded, are computed at refresh and stored in the model, so they do not change with user interaction and they increase model size. A calculated column serves slicers, filters, rows and columns; a calculated table serves intermediate data such as a UNION or CROSS JOIN. For values that respond to report filters, use a measure.

Create model calculations by using DAX

A measure is a DAX calculation evaluated on demand that changes with the filters and slicers in the report; its result is not stored in the model. Simple aggregations use SUM or AVERAGE over a column, and SUMX or AVERAGEX when an expression must be evaluated row by row. AVERAGE ignores empty cells but counts zeros.
CALCULATE evaluates an expression in a modified filter context. Its filters can be Boolean expressions, table expressions or modifiers such as REMOVEFILTERS, ALL or KEEPFILTERS; a filter on an already filtered column overwrites the existing one unless KEEPFILTERS is used. Without filters, it turns row context into filter context.
DAX time intelligence functions let you compare and accumulate values by day, month, quarter and year, with families such as DATESYTD and TOTALYTD, SAMEPERIODLASTYEAR, DATEADD and PARALLELPERIOD. Before using them, mark a table that contains a date column as a date table.
DAX offers statistical functions such as MEDIAN and MEDIANX (median of a column or of a per-row expression), PERCENTILE.INC (k between 0 and 1), RANKX, DISTINCTCOUNT, and the deviations STDEV.S for samples and STDEV.P for the entire population. Distinct counts are not additive in totals.
Semi-additive measures, such as balances or stock levels, do not aggregate the same way across every dimension: over time you take a point value, not the sum. CLOSINGBALANCEMONTH, QUARTER and YEAR evaluate the expression at the last date of the period, and LASTNONBLANK returns the last value with data in the current context.

Optimize model performance

Keep only columns that serve reports (filtering, grouping, summarizing) or model structure (relationships, calculations, security roles); the rest can be removed (vertical filtering). Also load as few rows as possible, filtering by time (for example relative history with parameters) or by entity (horizontal filtering). A smaller model refreshes faster and calculates faster.
Performance Analyzer records how long each visual takes and breaks it down into DAX query, DirectQuery, visual display and other time; results can be exported to JSON. From there you copy a visual's DAX query or run it in DAX query view, where you inspect and test measures with DEFINE MEASURE before updating the model.
Loading pre-summarized data (grouping by date, customer and product, or by date at month level) raises the grain of the fact table and is among the most effective ways to shrink a model. The trade-off is lost detail: you can no longer report by day or order line. It can be mitigated with DirectQuery tables for detail on a drillthrough page.

3. Visualize and analyze the data

25–30%

Create reports

Choose the visual by data and goal: column and bar charts to compare categories, line and area charts for trends over time, combo charts for measures with different ranges, donut and treemap for parts of a whole, scatter for relationships between two values, tables and matrices for exact values, and cards, KPIs and gauges for a key figure. Also consider the audience and the available space.
The visual formatting pane (paintbrush icon) offers options that depend on the visual type: colors, axes, data labels, titles, backgrounds and borders. You can reset a whole visual or just one section to default, apply style presets to tables and matrices, turn on total labels for stacked, combo and area charts, and use fx for conditional formatting. Formatting applied to a visual takes precedence over the theme.
The Copilot narrative visual summarizes the whole report, specific pages or selected visuals, and it reads what is on the canvas, not the semantic model, so visuals and axes should have clear names. The body isn't edited directly: it is adjusted with prompts, and footnotes highlight the source visuals. It doesn't update on its own when filtered; the reader uses the refresh button.
A theme applies colors and default formatting to all visuals: pick a built-in or organizational theme from the themes gallery (Design ribbon in Desktop, View and Theme in the service), or import, customize and download a JSON file. Use theme colors so visuals update when the theme changes; formatting on an individual visual stays until it is reset, and applying a built-in theme or importing one removes earlier theme customizations.
Conditional formatting changes colors, data bars, icons or links based on values, with three styles: gradient (numeric), rules, and field value (color names or hex codes, usually returned by a measure). In tables and matrices it applies to values, totals and subtotals, not to headers. For percentages, enter decimals and choose Number, and NaN values break automatic gradients and percent rules, so use DIVIDE.

Enhance reports for usability and storytelling

A bookmark saves the state of a page: filters and slicers, cross-highlight selections, sort order, drill location and object visibility from the Selection pane. The Data, Display and Current page options control what it applies, and bookmarks can be grouped, linked to buttons or shown through a navigator. If you change visibility after saving, update the bookmark.
A report tooltip is a page set to the Tooltip page size with the Tooltip switch turned on in Page information. Fields dragged into Tooltip fields make it appear on visuals that use them, and it can also be assigned manually or reverted to Auto. Tooltips are not interactive and screen readers cannot read them, so do not use them for essential information.
By default, visuals on a page cross-filter and cross-highlight each other. With Edit interactions, the author chooses the Filter, Highlight or None icon on each target visual, and the changes are saved with the report. Drilling does not affect other visuals unless it is set to apply to the whole page, and Reading view users cannot change interactions.
The page navigator syncs itself with the report pages: labels, order, and additions, removals and renames. The bookmark navigator shows all bookmarks or only one group, and can allow deselection with a launch bookmark. Buttons, shapes and images support actions such as Back, Page navigation, Bookmark or Web URL, and the destination can depend on a selection through conditional formatting.
Sort by Column lets one column define the order of another, such as a month number for month names; both columns must share the same granularity and the sort-by column cannot contain duplicate values. On visuals, the More options menu offers ascending, descending and axis sorting, and Shift enables multi-column sorting. Some visuals, such as cards or gauges, cannot be sorted.

Identify patterns and trends

Right-click a data point and Analyze offers Explain the increase or decrease and Find where this distribution is different. Explain compares the point with the previous one and shows which categories changed their contribution most; the result can be shown as a waterfall, scatter, 100% stacked column or ribbon chart, and in edit mode it can be added to the report. It is not available for the first data point.
Grouping (Ctrl+click and Group) gathers elements of a visual into a new category that you can edit with Edit groups. Binning sets a bin size for numeric or date fields, also on calculated columns but not on measures. Clustering, through Automatically find clusters on a scatter chart, creates a categorical field of groups of similar points and lets you set the number of clusters.
Key influencers analyzes a metric with fields in Explain by and ranks the factors that drive it; Top segments shows combinations of factors and Expand by sets the level of analysis for a measure. The decomposition tree takes a measure in Analyze and lets you drill into dimensions in any order, with high value or low value AI splits and lockable levels. The Narrative visual summarizes visuals as text.
The Analytics pane adds reference lines (constant, min, max, average, median, percentile), error bars, forecasts and anomalies. Error bars are defined by field, with absolute or relative bounds, or by percentage. Forecasting requires time data, is available only on line charts and supports settings such as forecast length and confidence interval.
Anomaly detection is turned on with Anomalies in the Analytics pane of a line chart that has a time series on the axis, with no legend or multiple values. It marks points outside the expected range, sensitivity controls how many are flagged and Explain by limits the fields used to explain them. It does not work together with forecast, min, max, average, median or percentile lines.

4. Manage and secure Power BI

15–20%

Create and manage workspaces and assets

Workspaces are created in the Power BI service, not in Power BI Desktop, and each needs a unique name. Optional settings include the contact list, a workspace OneDrive, and allowing Contributors to update the app. Access is granted through the Admin, Member, Contributor and Viewer roles, and only an Admin can delete the workspace.
An app packages workspace content with built-in navigation, and the workspace is the staging area: changes reach users only when the app is updated. You can define several audiences that see different content, and the reports behind a dashboard must be included so its tiles work. App users cannot modify the app content.
Publishing from Power BI Desktop sends the semantic model and its reports to a workspace, the same as uploading a .pbix file with Get data. Republishing replaces the existing semantic model, can break visuals if fields are renamed or deleted, and cannot switch storage mode from Import to DirectQuery. Publishing requires a role that can create content in the destination workspace.
A dashboard is a single page of tiles created in the Power BI service, not in Desktop, by pinning report visuals or an entire page as a live tile. It can combine different reports and semantic models and supports text, image, video, web content and streaming tiles. Dashboards do not support filtering or slicing, and data alerts apply only to cards, gauges and KPIs.
Use a workspace to collaborate as a team, an app to distribute curated read-only content to broad audiences, and sharing links or direct access for specific people. Link types are People in your organization, Specific people and People with existing access. Sharing a report also shares its semantic model, so restrict data with row-level security.

Secure and govern Power BI items

A workspace has four roles, Admin, Member, Contributor and Viewer, assignable to people or groups. Someone in several groups gets the highest role. Admin manages the workspace and its users, Member adds people with lower roles and publishes apps, Contributor creates and edits content, and Viewer only consumes.
Besides workspace roles, you can share a single item to give permissions on just that item to someone without a role. Sharing always grants at least Read. Links can target the organization, specific people or people with existing access, and permissions inherited from a role cannot be changed on the item's permissions page.
Access to a semantic model is controlled by the Read, Build, Reshare and Write permissions. Read lets users use existing reports, Build allows new content and Analyze in Excel, Reshare lets them grant access to others, and Write allows modifying and republishing. Workspace roles grant them implicitly and the rest is managed through sharing or the permissions page.
RLS roles are defined in Power BI Desktop with DAX filters that return true or false per row; they can be static or dynamic using USERPRINCIPALNAME. They are published with the model and validated with Test as role. RLS only restricts workspace Viewers, not Admin, Member or Contributor.
RLS role members are assigned in the Power BI service, from Security on the semantic model, not in Desktop. Users and Entra security groups, mail-enabled groups and distribution groups are supported, but not Microsoft 365 groups or service principals. Roles are additive, and external guests are best added directly by email.

Practice this certification