Power BI Data Analyst · 28% of the exam

Prepare the data: free practice questions

5 sample questions from our 70-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Microsoft exam.

1. A developer is building a Power BI dataflow in Power BI service to centralize data preparation logic for the entire organization. Several report developers will connect their Power BI Desktop models to this dataflow. What is the PRIMARY benefit of using a dataflow for shared data preparation compared to each developer performing the same transformations independently in their own Power BI Desktop files?

  • A. Dataflows allow reports to use DirectQuery mode against the underlying data source automatically.
  • B. Dataflows enable centralized, reusable transformation logic so that all downstream models consume consistently prepared data without duplicating M code across projects.✓ Correct
  • C. Dataflows eliminate the need for a data gateway when connecting to on-premises sources.
  • D. Dataflows store data in Azure SQL Database, making refresh faster than Power Query in Desktop.
Explanation

The primary benefit is reusability and consistency: transformation logic defined once in a dataflow is available to any number of downstream models, reducing duplication, maintenance overhead, and risk of inconsistency. DirectQuery mode is not automatically applied to dataflow-sourced tables; by default, dataflows load data into Azure Data Lake Storage Gen2 and downstream models import that data. Dataflows do not eliminate the gateway requirement for on-premises sources—a gateway is still needed to reach on-premises data from the dataflow. Dataflows store data in Azure Data Lake Storage Gen2, not Azure SQL Database, and the performance benefit is a secondary effect of pre-computing transformations, not the primary architectural reason to use them.

2. A Power BI developer is working in Power Query Editor. She right-clicks a step in the Applied Steps pane and does not see the 'View Native Query' option (it is greyed out). What does this most likely indicate?

  • A. The current step is the Source step, which never supports native query preview.
  • B. Query folding has broken at or before this step, so there is no foldable SQL expression to display.✓ Correct
  • C. The data source is a CSV file, which does not use a query language.
  • D. The developer does not have sufficient permissions on the data source to view the underlying SQL.
Explanation

Option B is correct: 'View Native Query' is available only when the step is fully foldable — meaning Power Query can represent it as a single native query (e.g., SQL) to send to the source. When folding has broken at a prior step, subsequent steps are processed in-memory by the M engine and there is no native query to show, so the option is greyed out. Option A is wrong: the Source step itself is typically foldable and 'View Native Query' is often available there. Option C is wrong: while CSV files don't support query folding, the question implies a scenario where a native query option would be expected (the developer notices it is greyed out), which is more diagnostic of broken folding on a foldable source. Option D is wrong: permissions on the data source are irrelevant to whether the native query preview option is available in Power Query Editor — that option reflects foldability, not authorization.

3. A Power BI developer wants to quickly identify which columns in a Power Query table have the highest proportion of empty or error values before loading data into the model. She has a table with 800,000 rows. Which Power Query Editor feature should she enable, and what additional step is required to ensure the statistics reflect the entire table rather than just the default preview?

  • A. Enable Column Distribution; no additional step is needed because it always scans the full dataset.
  • B. Enable Column Quality; change the profiling basis from 'Top 1000 rows' to 'Entire dataset' in the status bar.✓ Correct
  • C. Enable Column Profile; change the profiling basis from 'Top 1000 rows' to 'Entire dataset' in the status bar.
  • D. Enable Column Quality; increase the preview row limit to 10,000 rows in the Query Options dialog.
Explanation

Column Quality is correct because it shows the percentage of valid, error, and empty values per column — exactly what is needed to identify columns with high error or empty rates. By default, Power Query profiles only the top 1,000 rows; clicking the status bar text 'Column profiling based on top 1000 rows' and switching to 'Column profiling based on entire data set' ensures statistics reflect all 800,000 rows. Column Distribution is incorrect because it shows unique vs. distinct value counts and value distribution, not error/empty percentages. Column Profile shows detailed statistics for a single selected column, not a side-by-side overview for all columns. Increasing the preview row limit in Query Options is not a valid step; the profiling basis is changed via the status bar, not Query Options.

4. A company stores monthly sales results in 12 separate Excel files with identical schemas, one per month. A Power BI developer needs to combine all 12 files into a single table in Power Query. Which transformation approach is most appropriate?

  • A. Merge Queries — to join the files side-by-side on a key column
  • B. Append Queries — to stack the files on top of each other into one table✓ Correct
  • C. Pivot Columns — to rotate the month columns into rows
  • D. Group By — to aggregate each file into summary rows before combining
Explanation

Append Queries stacks tables with the same schema vertically (UNION-style), which is exactly what is needed to combine 12 identically structured monthly files into one table. Merge Queries performs a JOIN operation to combine tables horizontally based on matching key columns — appropriate for lookups, not for combining identical schemas. Pivot Columns rotates row values into column headers, which is the opposite of what is needed here. Group By aggregates rows by a specified column, used for summarization, not for combining separate tables.

5. A developer is preparing a Power Query solution for incremental refresh on a [FactSales] table in Power BI Premium. She has defined the RangeStart and RangeEnd parameters. She now needs to ensure that the filter applied to the [SaleDate] column will fold to the SQL Server source. Which of the following actions would BREAK query folding for the incremental refresh filter? Select TWO.

  • A. Filtering [SaleDate] using Table.SelectRows with a comparison against DateTime.From(RangeStart)
  • B. Adding a custom M column using Date.ToText([SaleDate], "yyyy-MM-dd") before the date filter step✓ Correct
  • C. Applying a 'Remove Duplicates' step on an unrelated [OrderID] column before the date filter✓ Correct
  • D. Using Table.SelectRows with [SaleDate] >= RangeStart and [SaleDate] < RangeEnd directly after the Source step
  • E. Renaming the [SaleDate] column to [TransactionDate] before the filter step
Explanation

Option B breaks folding because Date.ToText is a non-foldable M function that converts a date to text; once applied, all subsequent steps lose foldability. Option C breaks folding because Remove Duplicates is a transformation that SQL Server cannot natively represent as a pushdown operation, so it breaks the fold chain. Option A is correct syntax that typically folds. Option D is the canonical incremental refresh pattern and folds correctly. Option E (renaming a column) does fold in most SQL connectors because it translates to a column alias in the SELECT clause.

65 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free