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.
Microsoft Excel Excel Power Query
In Excel, which ribbon tab contains the Get & Transform Data group used to launch Power Query?