- What should the team configure?
Answer: An on-premises data gateway in standard mode, with the SQL data source and credentials mapped to the semantic model.
Whenever you see a question about on-premises data connectivity in Power BI service, your first instinct should be: how does cloud reach local infrastructure? The Power BI service runs in Microsoft's cloud and has no direct path to servers sitting behind a corporate firewall — that bridge must be explicitly built.
The solution is an on-premises data gateway in standard (enterprise) mode, which is exactly what option A describes. Standard mode installs as a shared Windows service, registers with the Power BI service, and lets multiple administrators manage it through the admin portal. You map the SQL Server data source to the gateway and store credentials centrally, so scheduled refresh works automatically without anyone being logged in locally.
Option B fails on two counts: personal-mode gateways are tied to a single user's machine and session, and they cannot be shared or co-administered — the question explicitly requires multiple admins to manage the connection. Option C is a trap for newcomers: Import-mode models do cache data in Power BI Desktop, but that cache is a snapshot that becomes stale the moment it's published; there's no mechanism to configure "unattended SQL access" through a local file upload. Option D exposes a private SQL Server to the public internet with anonymous authentication, which is both a severe security risk and technically impractical for most corporate environments — the service still wouldn't know how to route to an on-premises address.
As a study tip, remember the rule: personal gateway = one user, standard gateway = team/enterprise. Any question mentioning shared administration, scheduled refresh, or multiple users points to standard mode.
- How should you connect to the data?
Answer: Use the SQL Server connector and select the ReportingSales view in the Navigator.
When connecting Power BI to a SQL database, your guiding principle should be: use the most direct, governed data source that aligns with the permissions and business logic already in place. If an administrator has already built a view that enforces business rules, your job is to consume it — not recreate it.
The SQL Server connector in Power BI's Navigator displays both tables and views. Selecting ReportingSales directly (option C) is the correct approach because it reuses the administrator's logic without duplicating joins or filters, respects the service account's permissions (which cover the view but not underlying tables), and keeps governance centralized. When the administrator updates the view, your report inherits those changes automatically.
Option A introduces unnecessary fragility — Excel exports are a manual, scheduled workaround that creates a stale, disconnected copy of the data. This breaks the live governance model entirely. Option B is a trap for students who assume Power BI needs raw tables. Recreating the joins yourself duplicates logic the DBA already owns, risks inconsistencies, and would fail here anyway since the service account lacks permission to read the underlying tables directly. Option D is factually wrong and dangerous: DirectQuery does not bypass table-level permissions. SQL Server enforces permissions at the engine level regardless of how Power BI submits the query — attempting to query restricted tables will throw an error.
As a study tip, remember that Power BI's SQL Server connector treats views as first-class objects alongside tables. On exam questions involving governance, permissions, or pre-built SQL logic, always favor connecting to the existing view over rebuilding or exporting the data.
- Which workflow should you use?
Answer: Connect to Folder, filter the file list first, and then use Combine Files to define the shared transformation.
When working with a folder of files in Power Query, the order of operations matters enormously. The goal is to filter before combining so that only valid files ever enter your transformation pipeline — this is exactly what this question tests.
The right approach, answer A, is to connect to the folder, immediately filter out hidden system files and tilde-prefixed temporary files from the file list, and then invoke Combine Files. When you combine at that point, Power Query generates a sample file query and a shared transformation function. Every new file dropped into the folder is automatically picked up, filtered, and processed through that same function — zero manual updates required. This is the intended Power BI pattern for dynamic, folder-based file ingestion.
Answer B is a maintenance nightmare. Connecting to each CSV individually means you must manually update the append query every single day a new file arrives. It also doesn't scale and defeats the purpose of folder-based connectors entirely.
Answer C reverses the correct sequence. Combining before filtering means temporary and system files are processed first, potentially causing errors or corrupting your output. Cleaning up afterward is reactive and fragile — a malformed tilde file could break the sample file step before you ever get to remove it.
Answer D misunderstands incremental refresh. That feature controls which rows are loaded based on a date/time column; it does not discover new files in a folder or replace the Combine Files workflow.
As a study tip, remember the Power BI folder pattern as Filter → Combine: always clean the file list first, then let the sample-file function handle the rest automatically.
- Which connectivity mode should you choose?
Answer: DirectQuery mode, so report interactions query the SQL source rather than an imported copy.
When a question describes live, frequently updated data where users must always see current values and the organization wants to avoid managing scheduled refreshes, you're being tested on Power BI's connectivity modes — specifically the tradeoff between Import and DirectQuery.
DirectQuery mode (B) is the right choice here because every report interaction — a slicer selection, a visual click, a cross-filter — sends a live query directly to the Azure SQL Database and returns fresh results. No data is cached in the Power BI model, so there's no stale copy to worry about, and no refresh schedule to maintain. The passage even confirms the database can handle the interactive query load, which addresses DirectQuery's main limitation: source system performance pressure.
Option A fails because Import mode always stores a snapshot of data in Power BI's in-memory engine. Automatic page refresh controls how often that snapshot is replaced, but users are still interacting with a cached copy between refresh cycles — not live database values. This directly contradicts the requirement.
Option C is similarly flawed. Background preview is a Power Query Editor feature for previewing transformations during development; it has no effect on how a published report retrieves data at runtime. It doesn't make an imported dataset "live."
Option D is not a valid Power BI connectivity pattern at all. Reopening a CSV on each interaction isn't a supported or practical architecture — it's a trap designed to catch students who aren't thinking in terms of real Power BI features.
Study tip: On Power BI exam questions, the phrase "always see current values" or "no scheduled refresh" is a strong signal pointing to DirectQuery. Memorize this pairing.
- Which object should you select in the Navigator when connecting to the workbook?
Answer: Select the SalesData table because its defined range expands as rows are added.
When connecting Power BI to an Excel workbook, you need to distinguish between the different objects the Navigator can detect: worksheets, named ranges, and formatted Excel tables. Each behaves differently during refresh, and choosing the right one determines whether your model stays clean and dynamic over time.
Formatted Excel tables (created via Insert → Table in Excel) are the gold standard here. When you select SalesData in the Navigator (choice A), Power BI binds directly to that table object. As new rows are appended to SalesData in Excel, the table's boundaries expand automatically, and Power BI picks up those new rows on refresh — without touching any notes or free-form text sitting outside the table's defined range. This is exactly the behavior the scenario requires.
Choice B is tempting but wrong. Selecting the Sales worksheet imports everything on that sheet, including the notes below the table. Power BI does not automatically detect or exclude "note" cells — you would need manual transformation steps, which introduces fragility and ongoing maintenance.
Choice C compounds the problem by importing every worksheet first and then filtering. That's backwards workflow design — you're creating extra steps and potential for error when the cleaner solution already exists in the Navigator.
Choice D misrepresents how named ranges work. A standard named range is static — it references a fixed cell address like $A$1:$D$100. It does not automatically expand when new rows are added, unlike a formatted Excel table, which does.
Study tip: On Power BI questions involving Excel sources, always ask yourself: "Is this a formatted table, a named range, or a worksheet?" Formatted tables are the only Excel objects that grow dynamically, making them the reliable choice for refreshable, row-expanding datasets.
- Which privacy-level configuration is most appropriate?
Answer: Set the web source to Public and the SQL source to Organizational.
When working with Power Query privacy levels, your goal is to classify each data source based on its inherent sensitivity, not based on what the combined output looks like. Power Query uses these classifications to determine whether it's safe to fold queries together — preventing sensitive data from being inadvertently sent to less-trusted sources.
In this scenario, the web source contains publicly available currency exchange rates — data anyone can access freely. The SQL database holds confidential organizational sales data, meaning it's internal and not intended for public exposure. The correct move is D: set the web source to Public and the SQL source to Organizational. This accurately reflects each source's real-world sensitivity. Power Query will then correctly evaluate whether combining them risks leaking Organizational data to a Public endpoint — and will handle that safely without disabling privacy checks.
A is wrong because privacy levels don't need to match — they need to reflect reality. Forcing both to Organizational misclassifies the web source and serves no protective purpose. B is a classic trap: the output being a combined report doesn't make the inputs public. The SQL source is still confidential regardless of what you do with the result. Misclassifying it as Public removes the protection it needs and could cause Power Query to send internal data to external queries. C reverses the logic entirely — marking the open web source as Private and the sensitive internal database as Public is backwards and would mislead Power Query's data-combination safety checks.
As a study tip, remember: **assign privacy levels based on where the data comes from, not where it's going.**
- What should you do in Power Query to interpret the dates correctly without changing the source file?
Answer: Remove the automatic type step and use Change Type Using Locale with an appropriate locale.
Whenever you see a Power BI question about date parsing errors from CSV files, think about the root cause: a mismatch between how the source file encodes dates and how Power BI's locale interprets them. The solution must happen at the parsing stage in Power Query — not downstream in the model or display layer.
The right move is B: remove the auto-generated Changed Type step and replace it with Change Type Using Locale, selecting a locale whose date convention matches the source data (for example, English (United Kingdom) for dd/MM/yyyy). This tells Power Query exactly how to read the raw text before converting it to a date type, resolving the ambiguity at the source without touching the CSV.
A is wrong because formatting a column as dd/MM/yyyy in the model only changes how dates are displayed — it does nothing to fix dates that were already parsed incorrectly or generated errors during the type conversion step. Garbage in, garbage out.
C is wrong on multiple levels: converting dates to whole numbers and rebuilding them in DAX is unnecessarily complex, error-prone, and completely bypasses the proper Power Query tool designed for exactly this scenario. DAX measures are also not the right place to construct dimension values.
D is wrong because changing the report's display language affects visuals and UI text, not how Power Query parses raw date strings. Refreshing the same broken Changed Type step just repeats the same misinterpretation.
Study tip: On Power BI exam questions involving date locale issues, the answer almost always lives in Power Query's "Change Type Using Locale" — remember it's about parsing, not formatting.
- What should you implement in Power Query?
Answer: Create parameters for the server and database names and reference them in the SQL connection step.
When you see a question about managing multiple environments in Power BI, think about parameterization — the practice of externalizing values that change between deployments while keeping transformation logic stable. This is a foundational Power Query pattern for professional, maintainable report development.
Power Query parameters let you define named, editable values that can be referenced anywhere in your queries — including the connection step where you specify a server and database name. By creating two parameters (say, ServerName and DatabaseName) and referencing them in your SQL data source step, you can switch environments by simply updating those parameter values. The transformation logic downstream remains completely untouched, and if you use deployment pipelines, parameters can even be bound to environment-specific values automatically. This is exactly what C describes, making it the correct approach.
A is wrong because calculated columns are DAX constructs applied after data is loaded into the model — they have nothing to do with controlling where Power Query connects to retrieve that data in the first place.
B is wrong because duplicating every query and manually toggling versions is error-prone, hard to maintain, and completely ignores Power Query's built-in parameterization capability. It creates unnecessary redundancy and violates the goal of "editing only controlled values."
D is wrong because exporting to CSV introduces a fragile, manual file-swap workflow that bypasses the live database connection entirely — it doesn't scale and loses the benefits of querying a structured database.
As a study tip: on Power BI exam questions involving environment promotion or deployment, parameters are almost always the intended solution. Recognize the phrase "editing only controlled values" as a direct signal pointing to parameterization.
- Which approach should you use to load the product fields into Power BI?
Answer: Use the Web connector, authenticate as required, convert the JSON list to a table, and expand the records.
When connecting Power BI to an external data source, your first job is to match the connector to what the source actually provides. A plain REST endpoint returning JSON is neither a relational database, an OData service, nor an HTML page — so you need a connector flexible enough to handle raw JSON, plus the M query skills to shape it.
The Web connector is the right tool here. It retrieves the raw HTTP response, which Power BI surfaces as JSON content. From there, you convert the JSON list into a table using Convert to Table, then expand the record column to surface the individual fields — product identifier, name, and price — as separate columns. That's exactly what D describes, making it the correct approach.
A is wrong because the SQL Server connector expects a TDS protocol connection to a database engine, not an HTTP endpoint. Entering a URL as a server name won't retrieve JSON data.
B fails because the OData Feed connector is purpose-built for OData services, which expose structured entity sets and support query conventions like $filter and $top. The passage explicitly states this is not an OData service, so the connector won't know how to interpret the response.
C is a trap for students who conflate the Web connector's two modes. The HTML table selector only works when the webpage contains HTML <table> elements — the passage explicitly rules that out. Raw JSON responses require the table-conversion workflow, not the visual table picker.
As a study tip, remember: Web connector + JSON = Convert to Table + Expand Records. Anytime a question mentions a REST endpoint returning JSON that isn't OData, that three-step pattern is your answer.
- How should the data connection be redesigned?
Answer: Use the SharePoint Folder connector with the site URL, then filter to the workbook and access its content.
When Power BI reports need to be shared across a team, the data source path must be accessible to everyone who refreshes the report — not just the original developer. This question tests whether you understand how to replace a brittle local file path with a collaborative, cloud-based connection.
The right approach is A: using the SharePoint Folder connector with the site URL. This connector authenticates against SharePoint Online directly, so any authorized team member can refresh the report using their own organizational credentials — no local folder required. You filter down to the specific workbook within the connector, then navigate into its sheets or tables just as you would with a direct file connection.
B fails because replicating a user-profile directory on every team member's machine is fragile, unscalable, and defeats the purpose of storing files in SharePoint. If anyone's folder path differs even slightly, refresh breaks. C is a fundamental misunderstanding — Power BI doesn't "embed" live source data so that refresh becomes unnecessary; data must always be pulled from a live source to stay current, and embedding doesn't exist as a connection strategy in this context. D sounds plausible but is still wrong: pointing the Folder connector at a local synchronized directory means the path is still tied to one machine's file system. Organizational credentials don't fix a path that only exists on the analyst's computer.
Study tip: On Power BI exam questions involving shared or team-based refresh, always look for the answer that uses a cloud-native URL (SharePoint, OneDrive) rather than any local or user-specific path — that's the reliable, scalable pattern the exam rewards.
