Microsoft Power BI Quiz: Connecting To Data Sources
10 questions · exam conditions
0:00
Connecting To Data SourcesQuestion 1 of 10

An analyst receives an Excel workbook containing a worksheet named Sales. The worksheet includes a formatted Excel table named SalesData and several notes below the table. New transactions are added weekly by extending SalesData. The Power BI model must include new transactions after refresh without importing the notes.

Which object should you select in the Navigator when connecting to the workbook?

Select the SalesData table because its defined range expands as rows are added.
Select the Sales worksheet because Power BI will automatically exclude cells containing notes.
Select the workbook file and import every worksheet before filtering the unwanted rows.
Select a named range covering the current rows because named ranges always expand automatically.
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Connecting To Data Sources

Practice Connecting To Data Sources in Microsoft Power BI with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Connecting To Data Sources, giving you a quick way to practice the rules, question types, and explanations that matter most for Microsoft Power BI.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

An analyst receives an Excel workbook containing a worksheet named Sales. The worksheet includes a formatted Excel table named SalesData and several notes below the table. New transactions are added weekly by extending SalesData. The Power BI model must include new transactions after refresh without importing the notes.

Which object should you select in the Navigator when connecting to the workbook?

  1. Select the SalesData table because its defined range expands as rows are added. (correct answer)
  2. Select the Sales worksheet because Power BI will automatically exclude cells containing notes.
  3. Select the workbook file and import every worksheet before filtering the unwanted rows.
  4. Select a named range covering the current rows because named ranges always expand automatically.
Explanation: 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.

Question 2

A CSV file contains dates such as 04/07/2026, where the intended format is day/month/year. Power BI Desktop is configured with a locale that interprets dates as month/day/year. The automatically created Changed Type step either reverses valid dates or produces errors.

What should you do in Power Query to interpret the dates correctly without changing the source file?

  1. Keep the Changed Type step and format the column as dd/MM/yyyy in the model.
  2. Remove the automatic type step and use Change Type Using Locale with an appropriate locale. (correct answer)
  3. Convert the column to a whole number and reconstruct each date by using a DAX measure.
  4. Change the report's display language and refresh the existing Changed Type step without editing it.
Explanation: 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.

Question 3

A report connects to an Azure SQL Database containing frequently updated inventory data. Report users must see current database values whenever they interact with report visuals. The organization does not want to maintain an imported copy through scheduled refreshes. The database can support the expected interactive query workload.

Which connectivity mode should you choose?

  1. Import mode, with automatic page refresh configured for every report visual.
  2. DirectQuery mode, so report interactions query the SQL source rather than an imported copy. (correct answer)
  3. Import mode, with Power Query background preview enabled for the inventory table.
  4. A CSV export, with the report configured to reopen the file during each interaction.
Explanation: 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.

Question 4

An Excel workbook is stored in a SharePoint Online document library. An analyst initially connects to a locally synchronized copy by using a path under the analyst's user profile. Other team members must be able to refresh the report without reproducing that local folder structure.

How should the data connection be redesigned?

  1. Use the SharePoint Folder connector with the site URL, then filter to the workbook and access its content. (correct answer)
  2. Keep the local File connector and ask each team member to create the same user-profile directory.
  3. Embed the synchronized workbook in the PBIX file so refresh no longer depends on a source connection.
  4. Use the Folder connector against the analyst's synchronized directory and assign organizational credentials.
Explanation: 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.

Question 5

A public REST endpoint returns product information as JSON. It is not an OData service and does not provide an HTML page containing tables. The response consists of a JSON list in which each item is a record with product identifier, name, and price fields.

Which approach should you use to load the product fields into Power BI?

  1. Use the SQL Server connector, enter the endpoint as the server name, and select the JSON database.
  2. Use the OData Feed connector, select an entity set, and allow Power BI to infer the REST schema.
  3. Use the Web connector's HTML table selector and choose the table containing the product records.
  4. Use the Web connector, authenticate as required, convert the JSON list to a table, and expand the records. (correct answer)
Explanation: 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.

Question 6

A Power Query solution combines public currency exchange rates from a web source with confidential sales data from an organizational SQL database. Privacy checks must remain enabled, and the source classifications must accurately represent how each source may be combined.

Which privacy-level configuration is most appropriate?

  1. Set both sources to Organizational because Power Query requires matching privacy levels.
  2. Set both sources to Public because the final report contains combined data.
  3. Set the web source to Private and the SQL source to Public.
  4. Set the web source to Public and the SQL source to Organizational. (correct answer)
Explanation: 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.

Question 7

A report is developed against a SQL Server development database and promoted repeatedly to a production environment. The two databases have identical schemas but different server and database names. The query transformations should remain unchanged, and future environment switches should require editing only controlled values.

What should you implement in Power Query?

  1. Create calculated columns containing the production server and database names after data is imported.
  2. Duplicate every query for production and manually disable the development versions before publishing.
  3. Create parameters for the server and database names and reference them in the SQL connection step. (correct answer)
  4. Export development data to CSV and replace the files with production extracts during deployment.
Explanation: 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.

Question 8

A database administrator creates a SQL view named ReportingSales that already joins the required tables, excludes test transactions, and exposes only approved columns. The Power BI service account has permission to read the view but does not have permission to read its underlying tables directly. The report should use the administrator's governed definition with minimal duplicated logic.

How should you connect to the data?

  1. Request a recurring Excel export of the view and connect to the exported workbook.
  2. Use the SQL Server connector and select each underlying table before recreating the joins.
  3. Use the SQL Server connector and select the ReportingSales view in the Navigator. (correct answer)
  4. Use DirectQuery against the underlying tables because it bypasses table-level permissions.
Explanation: 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.

Question 9

A folder receives one sales CSV file each day. The files have the same structure, but the folder also contains hidden system files and temporary CSV files whose names begin with a tilde. You need one query that appends all valid sales files and applies the same cleanup steps to each file.

Which workflow should you use?

  1. Connect to Folder, filter the file list first, and then use Combine Files to define the shared transformation. (correct answer)
  2. Connect separately to every CSV file, append the queries, and manually update the append operation each day.
  3. Use Combine Files before filtering, and remove temporary-file records from the final combined sales table.
  4. Connect to the newest CSV only, and enable incremental refresh to discover the other files automatically.
Explanation: 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.

Question 10

A team publishes an Import-mode semantic model that retrieves data from an on-premises SQL Server. The model refreshes successfully in Power BI Desktop on the developer's computer, but scheduled refresh in the Power BI service cannot reach the server. Multiple administrators must be able to manage the connection.

What should the team configure?

  1. An on-premises data gateway in standard mode, with the SQL data source and credentials mapped to the semantic model. (correct answer)
  2. A personal-mode gateway on each report viewer's computer, with identical SQL credentials stored locally.
  3. A Power BI Desktop data cache uploaded with the report and configured for unattended SQL access.
  4. A public sharing link for the SQL Server, with anonymous authentication enabled in the service.
Explanation: 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.