Microsoft Excel Excel Power Query — Questions and Answers
Question 1: In Excel, which ribbon tab contains the Get & Transform Data group used to launch Power Query?
- Home
- Data (Correct answer)
- Formulas
- Insert
Correct answer: Data
Power Query connectors live in the Get & Transform Data group on the Data tab.
Power Query is exposed in Excel through the Data tab under the Get & Transform Data group. From there you choose Get Data, From Table/Range, From Text/CSV, and other connectors. Selecting a connector opens the Power Query Editor where transformations are applied.
Question 2: Which language does Power Query use to record the steps you apply in the Power Query Editor?
- DAX
- VBA
- M (Correct answer)
- SQL
Correct answer: M
Every applied step is written in the M formula language, visible in the Advanced Editor.
Power Query generates code in the M language (formally Power Query Formula Language) for each transformation step. You can view or edit this code through the Advanced Editor or the formula bar. DAX is used in Power Pivot and Power BI for calculated columns and measures, not for Power Query transformations.
Question 3: After loading a query to a worksheet, the source CSV file receives new rows. What is the quickest way to bring those rows into Excel?
- Re-import the file with a new query
- Click Refresh All on the Data tab (Correct answer)
- Manually copy the new rows into the table
- Delete the query and recreate it
Correct answer: Click Refresh All on the Data tab
Refreshing re-runs the saved steps against the current source data.
A key benefit of Power Query is that transformation steps are saved and can be re-executed. Clicking Refresh All (or refreshing a single query from the Queries & Connections pane) re-reads the source file and reapplies every step. This eliminates the need to reimport or manually update the data.
Question 4: Which Power Query transformation converts multiple columns such as Jan, Feb, and Mar into two columns named Attribute and Value?
- Pivot Column
- Group By
- Unpivot Columns (Correct answer)
- Split Column
Correct answer: Unpivot Columns
Unpivot turns wide crosstab data into a tall, normalized list.
Unpivot Columns takes the selected column headers and moves them into a single Attribute column, placing their values into a Value column. This reshapes wide data (one column per month) into a tall format suitable for PivotTables and analysis. Pivot Column performs the reverse operation.
Question 5: In the Power Query Editor, what happens when you delete a step from the middle of the Applied Steps list?
- Only that step is removed and later steps are re-evaluated (Correct answer)
- All steps after it are automatically deleted
- The query is reset to its source
- The step is hidden but still runs
Correct answer: Only that step is removed and later steps are re-evaluated
Removing a step removes only that step; subsequent steps still run and may error if they depended on it.
Applied Steps form a sequential chain where each step references the previous one. Deleting a middle step removes only that transformation, and Power Query attempts to evaluate the remaining steps. If a later step relied on a column created by the deleted step, that later step will show an error you must fix.
Question 6: Which option should you choose when you want a query's results available for other queries but do not want a table placed on any worksheet?
- Load to Table
- Only Create Connection (Correct answer)
- Load to PivotTable Report
- Load to Data Model only
Correct answer: Only Create Connection
Connection-only queries exist without loading output to the workbook.
Choosing Only Create Connection in the Import Data dialog stores the query definition without writing results to a sheet. This is common for staging queries that are later referenced, merged, or appended by other queries. It keeps the workbook lighter and avoids duplicate tables.
Question 7: 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 8: 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 9: 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 10: 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 11: 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 12: 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.
