Power BI Desktop opens onto a blank report canvas, but every meaningful workflow begins on the same ribbon button: Get data. Behind that button is a connector catalog that the engine treats as a layered abstraction, where the user selects a category, the category surfaces a list of connectors, and the connector launches an authentication flow plus a navigator dialog that lets a developer pick which objects to import. Treating this dialog as a single click is the most common cause of pain later, because the choices made in the first thirty seconds of a connection are nearly impossible to change without rebuilding queries from scratch.
The connector taxonomy contains several broad categories that the exam tests by name. File-based connectors include Excel, Text/CSV, XML, JSON, PDF, Parquet, and Folder; database connectors include SQL Server, Oracle, PostgreSQL, MySQL, Snowflake, Amazon Redshift, Google BigQuery, and a long tail of SQL dialects; Microsoft cloud services include Microsoft Fabric, Microsoft Dataverse, SharePoint Online List, and Power BI semantic models; online services include Azure Data Lake Storage, Azure Synapse Analytics, Azure SQL Database, SAP BW, and Salesforce; OData feeds and web connectors round out the picture. The categories are not decorative: they govern which authentication kinds the dialog offers, whether the connector exposes a Native Query window, and whether the navigator runs server-side folding. When a developer reaches for Get data and finds a connector missing from the default catalog, the option in More tells the engine to load a third-party connector certified through Microsoft's certification program; uncertified connectors run only with the Allow uncertified setting in Power BI Desktop's options.
Each connector follows the same lifecycle once selected. The credential prompt requests the authentication kind that the source accepts, the privacy level dialog asks how the connection should interact with other sources, and the navigator dialog lists the schemas, tables, and views that the credential principal can see. From the navigator, two buttons matter: Load and Transform Data. Load creates a query, runs it once, and writes the result to the data model immediately. Transform Data opens the Power Query Editor with a preview of the result and lets the developer compose steps before any data lands in the model. The trap is that Load runs the entire query at full row count against the source on the first refresh, so a developer who clicks Load on a billion-row fact table waits for the engine to read all billion rows before discovering that they wanted only the last thirty days. Pressing Transform Data instead and stamping a Table.FirstN or filter row before approval avoids that scenario.
let
Source = Sql.Database("srv01.contoso.example", "SalesDW"),
Sales = Source{[Schema = "dbo", Item = "FactSales"]}[Data],
LastThirtyDays = Table.SelectRows(
Sales,
each [OrderDate] >= Date.AddDays(DateTime.LocalNow(), -30)
)
in
LastThirtyDays
Resolve data import errors
Import errors surface at three different layers, and the layer determines the fix. The first layer is the credential layer: the source rejects the supplied credential, often because the authentication kind is wrong (Windows credentials passed to a service that wants Microsoft Entra ID, or Basic credentials sent to a port that expects OAuth2). The fix is the Data source settings dialog, where the developer clears the cached credential and supplies a new authentication kind. The second layer is the privacy layer, where the Formula Firewall raises an error of the form Formula.Firewall: Query 'X' (step 'Source') references other queries or steps, so it may not directly access a data source. This error means that the Power Query engine cannot reason about whether data from source A is allowed to be sent to source B. The fix is either to align the privacy levels of every connected source or to set Privacy Levels off in Options under Global, which is the developer-time workaround that production refresh should avoid.
The third layer is the data layer: an error inside a row, such as DataFormat.Error: We couldn't parse the input provided as a Date value, surfaces only on rows where the value violates the column type. The fix here is to inspect the offending rows by clicking the small red Errors link at the top of the column, then either to coerce the value with Table.TransformColumns and a try/otherwise expression, or to replace the error with a Replace Errors step. Power Query treats errors as values that propagate through downstream steps until they reach a step that reads the cell, at which point the error becomes visible. A common subtle case is the Changed Type step inserted automatically after the first row of preview: if the source occasionally returns a string in a numeric column, the type cast fails and the row turns into an error, even though the preview looked clean.
💡 Exam Trap: Formula.Firewall errors look like security errors but are caused by privacy-level evaluation, not by RBAC. The fix is to align privacy levels (set all participating sources to Organizational, for example), not to grant new permissions on the source.
💡 Exam Trap: DataSource.Error errors with a numeric code such as -2147467259 almost always indicate a credential or network failure (firewall, expired token, blocked port). DataFormat.Error indicates that the credentials and network are healthy but the bytes do not parse against the declared type.
Identify when to use reference or duplicate queries and the resulting impact
Reference and duplicate are two ways to start a new query from an existing one, and they produce drastically different refresh shapes. Duplicate copies the entire step list of the source query into the new query and breaks the link, so the two queries evaluate independently and each issues its own request against the source. Reference creates a new query whose first step is = SourceQuery, which causes the engine to read the result of the source query and then apply additional steps. From a developer's point of view the editor looks identical, but at refresh time the difference is fundamental.
Use Reference when you need to materialize a single canonical source once and then derive multiple downstream tables from it (for instance, one base query that reads the raw orders table, and three references that produce daily orders, monthly orders, and customer-level orders). When folding is preserved, the references still fold back to the source individually, but the developer has expressed a logical lineage that the maintainer can read at a glance. Use Duplicate when you need an editor-time starting point for a divergent transformation and you want the new query to evolve without dragging the original along. The cost of Duplicate is that it doubles the number of source queries: in Import mode the engine reads the same source twice on every refresh.
A subtle point catches developers who use Reference inside a query that disables Enable load on the base. When the base query is set to Enable load = false (the silver-platter pattern, where the base query is staging and is not visible in the model), the referencing queries still see its result correctly, but the base query is no longer in the refresh refresh list directly. Refresh fires through the references, which trigger the base, and the base runs once per reference unless folding caches the result at the source. This is why staging queries should still be set to Include in report refresh = false to prevent the Service from trying to refresh a non-loaded query.
⚠️ Anti-Pattern: Using Duplicate when Reference is correct. The model refreshes look fine in Desktop, then on the Service the source database shows three identical queries running concurrently for what should have been one read. Switch to Reference to read once and fan out.
🎯 Scenario: A developer connects to a Fact_Orders table that contains 90 million rows. They duplicate the query four times to produce four reporting tables (orders, returns, shipments, cancellations). On Desktop the refresh takes 12 minutes. The fix is to keep one base query with Enable load = false, then reference it four times. The source now runs a single scan and the engine partitions the result.
Choose Reference or Duplicate: decision anchor
Choose Reference when the new query should reflect future changes to the source query and when source-side evaluation should happen once. Choose Duplicate when the new query is a starting point that must diverge permanently and where future edits to the original must not propagate.