Microsoft Power BI Quiz: Appending Queries
10 questions · exam conditions
0:00
Appending QueriesQuestion 1 of 10

In Power Query, the North query contains the columns CustomerID, Amount, and OrderDate. The South query contains the columns OrderDate, CustomerID, Amount, and SalesRegion. You append South to North without performing any other transformations.

What will the appended query contain?

Four columns matched by name; North rows will contain null in SalesRegion.
Four columns matched by position; North CustomerID values will appear under OrderDate.
Three columns inherited from North; the SalesRegion values from South will be discarded.
Four columns matched by name; North rows will repeat their CustomerID in SalesRegion.
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Appending Queries

Practice Appending Queries 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 Appending Queries, 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

In Power Query, the North query contains the columns CustomerID, Amount, and OrderDate. The South query contains the columns OrderDate, CustomerID, Amount, and SalesRegion. You append South to North without performing any other transformations.

What will the appended query contain?

  1. Four columns matched by name; North rows will contain null in SalesRegion. (correct answer)
  2. Four columns matched by position; North CustomerID values will appear under OrderDate.
  3. Three columns inherited from North; the SalesRegion values from South will be discarded.
  4. Four columns matched by name; North rows will repeat their CustomerID in SalesRegion.
Explanation: When you append queries in Power Query, the engine matches columns by name, not by position. This is a critical distinction — think of it like a SQL UNION where column names determine alignment, not column order. Since North has three columns (CustomerID, Amount, OrderDate) and South has four (OrderDate, CustomerID, Amount, SalesRegion), the append operation creates a unified schema containing all four distinct column names. Rows from North fill in perfectly for the three columns they share with South, but because North has no SalesRegion column, those cells become null for every North row. South rows populate all four columns normally. This makes A the correct answer. B is wrong because Power Query does not match by position during an append — that's a common misconception borrowed from Excel-style thinking. If it did match by position, North's CustomerID values would incorrectly land under OrderDate (South's first column), which would corrupt your data silently and dangerously. C is wrong because the append does not limit the output to the column count of the first (North) query. Power Query performs a full outer union of all column names, so SalesRegion is preserved — not discarded. D is wrong because null values are generated for missing data, not copied from another column. CustomerID values don't "repeat" into SalesRegion; that would require an explicit transformation step. A helpful rule of thumb: in Power Query, Append = name-matched union, Merge = join. When you see append questions, always ask yourself which columns are unique to each query — those become null-filled for the other side.

Question 2

Two staging queries must be appended. CurrentSales has a column named Order ID, while ArchiveSales has the corresponding column named OrderID. All other column names are identical. After the append, the result contains both Order ID and OrderID, with nulls distributed between them.

Which action should you take to produce one populated order identifier column while preserving all rows?

  1. Rename OrderID to Order ID in ArchiveSales before performing the append. (correct answer)
  2. Reorder OrderID to the same ordinal position as Order ID before the append.
  3. Append the queries first, and then remove whichever identifier column contains more nulls.
  4. Change both identifier columns to the Whole Number data type before the append.
Explanation: When Power Query appends two tables, it matches columns by name only — not by position or data type. If two columns have even slightly different names, like Order ID (with a space) versus OrderID (no space), the append treats them as separate columns and fills each with nulls wherever the other table has data. Your goal before appending is to make those column names identical. Renaming OrderID to Order ID in the ArchiveSales staging query — answer A — solves this at the root. Once both tables share the exact same column name, Power Query merges them into a single, fully populated column during the append. No nulls, no duplicate columns, no data loss. Answer B is a common misconception: column order is irrelevant during an append. Power Query matches on name, not ordinal position, so reordering does nothing to reconcile the naming mismatch. Answer C is a destructive workaround. Removing whichever column has "more nulls" would discard real order ID values from one of the two tables, violating the requirement to preserve all rows. You'd lose data, not just nulls. Answer D changes the data type, not the column name. Power Query still sees two differently named columns after a type conversion, so the same null-split problem persists. Data types affect how values are stored and calculated, not how columns are matched during an append. The key study takeaway: in Power Query, appends join on exact column name matches. Any rename, capitalization difference, or spacing difference creates a new column. Always align column names in your staging queries before combining tables.

Question 3

JanuarySales contains 12,000 rows, and FebruarySales contains 10,000 rows. Due to a source-system correction, 150 transaction rows appear in both queries with identical values. You append FebruarySales to JanuarySales and apply no additional steps.

What row count should you expect, and why?

  1. 10,000 rows, because append retains only rows from the most recently selected query.
  2. 21,850 rows, because append automatically removes rows that are identical across queries.
  3. 12,000 rows, because append updates matching January rows with values from February.
  4. 22,000 rows, because append retains duplicate rows unless they are explicitly removed. (correct answer)
Explanation: When working with Power Query's Append operation, the key concept to anchor on is that appending is a union-all operation — it stacks rows from one query on top of another without any automatic deduplication or matching logic. In this scenario, JanuarySales has 12,000 rows and FebruarySales has 10,000 rows. Appending them simply combines both full datasets: 12,000+10,000=22,000 rows12{,}000 + 10{,}000 = 22{,}000 \text{ rows}. The 150 duplicate rows are retained because Power Query makes no effort to detect or remove identical rows during an append. That makes D the correct answer. The distractors each reflect a specific misconception worth understanding. A is wrong because append does not discard one query in favor of the other — both datasets contribute all their rows to the result. B describes behavior closer to a distinct or remove duplicates step, which is a separate, explicit action you must apply manually; appending alone never eliminates matching rows. C confuses append with a merge (join) operation — merging can match rows across queries and update values, but appending has no concept of "matching" rows at all. A useful mental model: think of append like stacking two printed spreadsheets on top of each other. Every row from both sheets ends up in the pile, regardless of whether any rows look identical. If you want duplicates removed, you must explicitly add a Remove Duplicates step after appending. On Power BI exam questions, always distinguish between Append (stack rows) and Merge (join columns) — these two operations are frequently tested and commonly confused.

Question 4

You append two large inventory queries containing 1.2 million total rows. The Quantity column is intended to be a whole number. Column quality reports 100% valid, but a source audit identifies nonnumeric text in Quantity near row 800,000. Power Query is currently profiling columns based on the top 1,000 rows.

What should you do to validate the appended Quantity column most reliably in the editor?

  1. Move Quantity to the first column because profiling evaluates leading columns across all rows.
  2. Keep profiling the top 1,000 rows and sort Quantity alphabetically before reviewing column quality.
  3. Set column profiling to the entire data set, refresh the preview, and inspect Quantity errors. (correct answer)
  4. Remove duplicate inventory rows because duplicates prevent later rows from being included in profiling.
Explanation: Whenever you see a question about data profiling in Power Query, the first thing to recognize is that profiling scope controls how much of your dataset is actually analyzed — and the default setting is a critical limitation to know for the exam. By default, Power Query profiles only the top 1,000 rows of a dataset. This means any data quality issues sitting deeper in your query — like nonnumeric text near row 800,000 — will be completely invisible to column quality, column distribution, and column profile views. The fix is straightforward: navigate to the View tab in the Power Query Editor, select Column profiling based on entire data set, then refresh the preview. Once you do this, Power Query will scan all rows and surface errors in the Quantity column, making option C the correct and most reliable approach. Option A is a misconception — profiling scope is not determined by column position. Moving Quantity to the first column does nothing to extend profiling beyond row 1,000. Option B compounds the problem rather than solving it: sorting alphabetically still operates within the top 1,000 rows, so the nonnumeric values near row 800,000 remain invisible. You'd be inspecting a false clean result. Option D introduces a completely unrelated concept — duplicate rows don't suppress later rows from profiling, and removing them wouldn't expose errors in unprofiled data anyway. A reliable study tip: on Power BI exam questions involving data quality or missing errors in large datasets, always check whether profiling scope is the culprit. The phrase "top 1,000 rows" is a red flag that the default setting may be hiding problems.

Question 5

A monthly billing query contains InvoiceID, InvoiceDate, and Amount. A legacy billing query contains InvoiceID, BillingDate, Amount, and Salesperson. BillingDate has the same business meaning as InvoiceDate. The final appended query must contain InvoiceID, InvoiceDate, Amount, and Salesperson.

Which sequence produces the required schema with the least risk of losing data?

  1. Reorder BillingDate beneath InvoiceDate, add Salesperson to the monthly query, and then append.
  2. Append first, remove BillingDate, and retain InvoiceDate as the only date column in the result.
  3. Rename BillingDate to InvoiceDate, add a null Salesperson column to the monthly query, and then append. (correct answer)
  4. Merge the queries on InvoiceID, expand BillingDate and Salesperson, and remove the original date column.
Explanation: When appending queries in Power BI, the engine matches columns by name, not by position or business meaning. If two columns hold the same data but have different names, the append operation creates two separate columns, splitting your data across them — a silent data loss risk that's easy to miss. The safest approach is to align schemas before appending. Option C does exactly this: renaming BillingDate to InvoiceDate ensures both queries share the same column name, so the append merges those values into a single column correctly. Adding a null Salesperson column to the monthly query guarantees that column exists in both sources, so no rows are dropped or misaligned when the queries combine. This is the least-risk path to the required four-column output. Option A is tempting but wrong — reordering columns doesn't help the append engine, which matches by name, not position. BillingDate and InvoiceDate would still produce two separate date columns in the result, losing the alignment you need. Option B is dangerous because you're appending first with mismatched column names, which creates two date columns populated with nulls on the wrong sides. Removing BillingDate afterward discards all the legacy dates, losing real data. Option D uses a Merge (join) instead of an Append. Merging on InvoiceID combines rows that match, not rows from different time periods — this would exclude invoices that exist in only one source, introducing data loss and producing a fundamentally different result than a union-style append. Study tip: Always rename mismatched columns and add placeholder null columns before appending. Think of it as "align then combine" — schema alignment is the gate, append is the action.

Question 6

A query named ConsolidatedSales was created by using Append Queries as New on Sales2025 and Sales2026. Sales2026 is later updated with a filter that removes canceled orders. No changes are made directly to ConsolidatedSales.

What will occur when the semantic model is refreshed?

  1. ConsolidatedSales will retain the old Sales2026 rows because Append Queries as New creates a static copy.
  2. ConsolidatedSales will reevaluate Sales2026 and exclude its canceled orders from the appended result. (correct answer)
  3. ConsolidatedSales will exclude all Sales2026 rows because changing a dependency invalidates the append step.
  4. ConsolidatedSales will remain unchanged until the append operation is manually configured a second time.
Explanation: When you see a question about Append Queries as New in Power Query, the key concept to focus on is query dependency, not data isolation. "As New" means a separate query is created, but that query still references its source queries dynamically — it is not a snapshot or static copy. Here's how it works: ConsolidatedSales was built by appending Sales2025 and Sales2026. Internally, its M code pulls from both source queries at refresh time. When Sales2026 is updated with a filter removing canceled orders, that filter becomes part of Sales2026's own transformation steps. Because ConsolidatedSales depends on Sales2026, it will automatically inherit those updated results on the next refresh — meaning canceled orders are excluded from the final appended output. That makes B correct. A is wrong because it assumes "Append Queries as New" creates a static, disconnected copy. It doesn't — the word "New" just means the append result becomes its own named query, not that the data is frozen at creation time. C is wrong because changing a source query does not invalidate or break the append operation. Power Query gracefully re-executes the dependency chain; it doesn't drop all rows from a modified source. D is wrong because no manual reconfiguration is needed. Power Query's query folding and dependency resolution handle upstream changes automatically on refresh — that's one of its core strengths. Study tip: On Power BI exam questions, always ask yourself whether data transformations are dynamic references or static snapshots. In Power Query, almost everything is dynamic — refreshing re-executes the full dependency chain from source to output.

Question 7

You must append a historical sales query to a current sales query. CurrentSales stores Amount as a decimal number. HistoricalSales stores Amount as text, and some values contain currency symbols and thousands separators. The final Amount column must support aggregation, and valid historical amounts must not be lost.

Which preparation approach should you use?

  1. Clean the historical text values, convert Amount to a numeric type, validate errors, and then append. (correct answer)
  2. Append the queries first, remove all rows that produce conversion errors, and then convert Amount.
  3. Convert the current numeric Amount to text, append the queries, and leave the result as text.
  4. Reorder the Amount columns into matching positions, append the queries, and rely on automatic conversion.
Explanation: When combining queries with mismatched data types in Power Query, your guiding principle should be: clean and transform data before merging, not after. This protects data integrity and ensures aggregation works correctly in the final model. The right approach here is A. Because HistoricalSales stores Amount as text with currency symbols (like "$1,200.00") and thousands separators, you must first strip those characters using transformations like Replace Values or a custom cleaning step, then convert the column to a decimal type. After conversion, you validate for errors — rows where text couldn't be parsed — so you can investigate rather than silently discard them. Only then do you append to CurrentSales, resulting in a unified numeric Amount column that supports SUM, AVERAGE, and other aggregations. B is dangerous because appending first and removing error rows afterward means you permanently delete historical records without ever knowing what failed or why. You lose valid business data with no audit trail. C is a business logic failure. Converting CurrentSales Amount to text so both columns "match" destroys the numeric nature of the data. A text column cannot be aggregated, which directly violates the requirement that the final column supports aggregation. D relies on Power Query's automatic type coercion, which does not exist reliably. Power Query won't automatically strip currency symbols or parse formatted text — it will generate errors or nulls instead. Study tip: On Power BI exam questions involving data type mismatches, always favor the answer that cleans data upstream before combining. Cleaning after merging is a red flag — it risks data loss and complicates error handling.

Question 8

A Power BI file contains three validated staging queries named Sales_US, Sales_CA, and Sales_MX. Other model queries reference each staging query independently. You need a consolidated query without changing the transformation steps or outputs of the three staging queries.

Which Power Query operation should you use?

  1. Select Append Queries as New and append the three staging queries into a new query. (correct answer)
  2. Select Append Queries on Sales_US and add the other two queries to its existing steps.
  3. Select Merge Queries as New and match the three queries using their transaction identifiers.
  4. Duplicate Sales_US twice and replace each duplicate's source with another staging query.
Explanation: When working with Power Query consolidation tasks, the key distinction to understand is Append vs. Merge and "as New" vs. in-place operations. Append stacks tables vertically (same columns, more rows), while Merge joins tables horizontally (like a SQL JOIN). The "as New" variant creates a separate output query, leaving the originals untouched. Since you need to consolidate three staging queries into one without modifying their existing transformation steps or outputs, Append Queries as New is the right tool. It creates a brand-new query that unions all three tables row-by-row, while Sales_US, Sales_CA, and Sales_MX remain completely unchanged — their steps, outputs, and downstream references stay intact. That's option A, the correct answer. Option B is tempting but dangerous: using Append Queries (without "as New") on Sales_US modifies that query directly by adding steps to it. This changes the transformation logic of a validated staging query, violating the requirement and potentially breaking other queries that depend on Sales_US independently. Option C describes a Merge operation, which performs a JOIN based on matching keys — this combines columns, not rows. Regional sales tables with identical structures should be stacked vertically, not joined horizontally. Merging here would produce incorrect results. Option D is an unnecessary workaround. Duplicating Sales_US and swapping its source doesn't consolidate the queries — it just creates separate copies, and you'd still need an append step to combine them, adding complexity with no benefit. Study tip: Whenever you see "consolidate" or "combine rows" with a need to preserve source queries, think Append as New. Reserve Merge for when you're matching on keys to combine columns.

Question 9

You append quarterly queries that should expose only CustomerID, OrderDate, and Revenue. Q4 unexpectedly includes an additional internal column named AuditNote. Future quarters might omit one of the required columns. You need a stable three-column output, and a missing required field should appear as null rather than causing the query to fail.

Which preparation strategy best satisfies the requirement?

  1. Append all input columns first and keep every column so that future schema changes from any quarter remain fully visible in the output.
  2. Select the three required columns in each input using missing-field handling that substitutes null, and then append. (correct answer)
  3. Delete AuditNote only from Q4, assume all future quarterly inputs will retain the required fields, and append without further schema enforcement.
  4. Reorder the required columns to the same ordinal positions in each input and append, relying on positional alignment to produce a consistent output schema.
Explanation: When appending queries in Power BI that may have inconsistent schemas, your goal is defensive transformation: shape each source into a known, controlled structure before combining them, so downstream outputs remain predictable regardless of what any individual source contains. The strategy that achieves this is B. By selecting only the three required columns (CustomerID, OrderDate, Revenue) in each quarterly query individually — using a pattern that returns null when a column is absent rather than throwing an error — you guarantee every input table has exactly the same schema before the Append step. The final output is always three columns, extra columns like AuditNote are simply never selected, and a missing required column gracefully becomes null instead of breaking the refresh. A fails because keeping every column means AuditNote (and any future unexpected columns) pollute your output. You lose schema control entirely and expose consumers to unstable, unpredictable fields. C is fragile by design. Manually deleting AuditNote from Q4 is a one-time fix that doesn't scale. If Q5 introduces a different rogue column, or if a required column disappears, the query breaks or silently produces wrong results — exactly the failure modes the requirement asks you to prevent. D relies on positional alignment, which is one of the most dangerous assumptions in data integration. If any quarter reorders columns or omits one, positional matching will silently map the wrong data to the wrong field — a subtle, hard-to-detect error far worse than a visible failure. Study tip: In Power BI schema questions, always favor explicit column selection with null-substitution over positional tricks or manual cleanup — it's the only approach that handles both extra and missing fields robustly.

Question 10

An appended query combines OnlineOrders and StoreOrders. Both initially contain a column named CustomerKey. A developer later renames CustomerKey to CustomerID only in StoreOrders. No corresponding rename is added to OnlineOrders or to the appended query.

What is the most likely result after the next refresh?

  1. The refresh automatically matches the columns because their values and data types remain equivalent.
  2. The result contains only CustomerKey because the first query permanently defines the complete append schema.
  3. The result contains only CustomerID because the most recently changed query overrides the earlier schema.
  4. The result contains CustomerKey and CustomerID, with nulls for rows from the source lacking each column. (correct answer)
Explanation: When working with appended queries in Power Query, you need to understand how the append operation handles schema mismatches — specifically, what happens when the two source queries don't share identical column names. Power Query's append operation is column-name-driven. It aligns rows from both tables by matching column names exactly. When a column name exists in one source but not the other, Power Query doesn't try to guess or merge them — it creates both columns in the output and fills the missing values with nulls. So when CustomerKey exists only in OnlineOrders and CustomerID exists only in StoreOrders, the appended result contains both columns: StoreOrders rows get a null in CustomerKey, and OnlineOrders rows get a null in CustomerID. That makes D the correct answer. Answer A is wrong because Power Query doesn't perform any value-level or type-level comparison to determine whether two differently named columns "should" be the same — column matching is strictly by name. Answer B reflects a common misconception that the first query in an append permanently dictates the full schema. In reality, the second query can introduce new columns, and the append will surface them alongside the original ones. Answer C is equally wrong — there's no override mechanism where a later query silently replaces an earlier column definition. Neither query dominates; both contribute their column names to the union. As a study tip, remember that Power Query appends work like a full outer join on column names: every distinct column from either table appears in the result, and gaps are filled with nulls. This is a frequently tested behavior on the Power BI exam.