CertifyDeckCargando contenido…
The official Power BI Data Analyst syllabus, organized by domain. Check off each point as you review it.
Inicia sesión para guardar tu progreso entre sesiones.
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.
Data sources in Power BI Desktop ↗Connect to semantic models in Power BI ↗
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.
Authentication in desktop apps - Power Query ↗Privacy Levels in Power Query ↗
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).
DirectQuery in Power BI: When to Use, Limitations, Alternatives ↗Direct Lake overview - Microsoft Fabric ↗
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.
Parameters - Power Query ↗Edit parameter settings in the Power BI service ↗
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.
Dealing with errors in Power Query ↗Fill values in a column ↗
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.
Dealing with errors in Power Query ↗Power Query Excel connector ↗
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.
Add a custom column - Power Query ↗Add a conditional column - Power Query ↗
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.
Pivot columns feature description - Power Query ↗Unpivot columns - Power Query ↗
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.
Parse text as JSON or XML - Power Query ↗Power Query JSON connector ↗
In a star schema, dimension tables describe business entities, have a unique key column and are used for filtering and grouping; fact tables store observations or events with dimension keys and numeric columns that are summarized. If the source is denormalized, Power Query is used to split it into normalized tables. Avoid mixing both types in one table and load facts at a consistent grain.
Duplicate creates an independent copy of the query; Reference creates a new query that starts from the steps of the original, and changes to the original flow down to the referenced query. Referencing avoids repeating logic, but each referencing query runs the base steps separately, which can slow refresh and burden the source; Table.Buffer does not prevent it. A dataflow can encapsulate the source so it is evaluated only once.
Using the Queries pane - Power Query ↗Referencing Power Query queries ↗
Merge joins two tables on matching values from one or more columns; the join kind (left outer, right outer, full outer, inner, left anti or right anti) determines which rows are kept, and left versus right position matters. The matching columns must have the same data type. Append stacks tables by matching column names and fills missing columns with null; there is also an as-new variant.
Merge Queries Overview - Power Query ↗Append Queries - Power Query ↗
A model relationship is based on one column in each table and at least one must have unique values; otherwise you get the error that one of the columns must have unique values. When a dimension has no unique column you add a surrogate key, for example with a Power Query index column, and merge it into the many-side query. In a dimension that keeps versions, the business key is not unique.
Understand star schema and the importance for Power BI ↗Create and Manage Relationships in Power BI Desktop ↗
Intermediate queries, such as those that only feed a merge, do not need to be loaded to the model: clear Enable load in the query context menu or in Properties. For data that rarely changes, clear Include in report refresh; those queries are also excluded from automatic refresh in the service. Close & Apply loads the changes, and the steps do not modify the source data.
Managing query refresh ↗Query Overview in Power BI Desktop ↗