Microsoft Excel Excel Power Query 2 — Questions and Answers
Question 1: You have a Sales query and a Products query that share a ProductID column. Which Power Query operation adds product details to each sales row?
- Append Queries
- Merge Queries (Correct answer)
- Group By
- Transpose
Correct answer: Merge Queries
Merge performs a join between two queries on matching key columns.
Merge Queries combines two tables horizontally by matching values in one or more key columns, similar to a SQL join. After merging you expand the nested table column to pull in the desired fields. Append Queries, by contrast, stacks rows from tables with the same structure vertically.
Question 2: Which join kind in Merge Queries keeps all rows from the first table and only matching rows from the second?
- Inner
- Full Outer
- Left Outer (Correct answer)
- Right Anti
Correct answer: Left Outer
Left Outer preserves every row of the first (left) table.
A Left Outer join returns all rows from the first table and the matching rows from the second; non-matching rows receive null values. Inner returns only matches from both. Full Outer returns all rows from both tables, and Anti joins return only the rows that do not match.
Question 3: A column imported as text contains values like 1,234.50 that must be summed. What should you do in the Power Query Editor?
- Use Replace Values to remove commas and change the data type to Decimal Number (Correct answer)
- Leave it as text because Excel will sum text automatically
- Use Split Column by delimiter
- Apply Group By on the column
Correct answer: Use Replace Values to remove commas and change the data type to Decimal Number
Numeric text must be cleaned of separators and converted to a numeric data type.
Power Query treats text values as strings and cannot sum them. Removing thousand separators with Replace Values (or using a locale-aware type change) and then setting the column's type to Decimal Number converts the values to real numbers. Once numeric, aggregations and PivotTable sums work correctly.
Question 4: Which Power Query feature lets you combine all CSV files in a folder into a single table with one query?
- From Web
- From Folder with Combine Files (Correct answer)
- From Table/Range
- Data Validation
Correct answer: From Folder with Combine Files
The Folder connector plus Combine Files builds a sample transform and applies it to every file.
Get Data > From Folder lists all files in a directory. Clicking Combine Files creates a helper function based on a sample file and applies it to each file, appending the results into one table. New files dropped into the folder appear on the next refresh.
Question 5: In the Power Query Editor, which command adds a column that increments 1, 2, 3, and so on for each row?
- Custom Column
- Conditional Column
- Index Column (Correct answer)
- Column From Examples
Correct answer: Index Column
Index Column creates a sequential numeric column starting from 0 or 1.
Add Column > Index Column inserts a column of sequential integers, with options to start from 0, from 1, or from a custom starting value and increment. Index columns are useful for preserving original row order and for referencing previous or next rows in calculations.
Question 6: What does the Group By transformation with the Sum operation on an Amount column produce?
- A running total on each row
- One row per group with the total Amount (Correct answer)
- A percentage of the grand total
- A sorted copy of the original table
Correct answer: One row per group with the total Amount
Group By aggregates rows into one summary row per unique group value.
Group By collapses the table so that each unique value (or combination) in the grouping column becomes a single row. The chosen aggregation, such as Sum of Amount, is calculated for each group. Other aggregation options include Count, Average, Min, Max, and All Rows.
Microsoft Excel Excel Power Query 2
You have a Sales query and a Products query that share a ProductID column.
Which Power Query operation adds product details to each sales row?