Excel Power Query Flashcards

In Excel, which ribbon tab contains the Get & Transform Data group used to launch Power Query?

Answer: Data

Power Query connectors live in the Get & Transform Data group on the Data tab.

Which language does Power Query use to record the steps you apply in the Power Query Editor?

Answer: M

Every applied step is written in the M formula language, visible in the Advanced Editor.

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?

Answer: Click Refresh All on the Data tab

Refreshing re-runs the saved steps against the current source data.

Which Power Query transformation converts multiple columns such as Jan, Feb, and Mar into two columns named Attribute and Value?

Answer: Unpivot Columns

Unpivot turns wide crosstab data into a tall, normalized list.

In the Power Query Editor, what happens when you delete a step from the middle of the Applied Steps list?

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.

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?

Answer: Only Create Connection

Connection-only queries exist without loading output to the workbook.

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?

Answer: Merge Queries

Merge performs a join between two queries on matching key columns.

Which join kind in Merge Queries keeps all rows from the first table and only matching rows from the second?

Answer: Left Outer

Left Outer preserves every row of the first (left) table.

A column imported as text contains values like 1,234.50 that must be summed. What should you do in the Power Query Editor?

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.

Which Power Query feature lets you combine all CSV files in a folder into a single table with one query?

Answer: From Folder with Combine Files

The Folder connector plus Combine Files builds a sample transform and applies it to every file.

In the Power Query Editor, which command adds a column that increments 1, 2, 3, and so on for each row?

Answer: Index Column

Index Column creates a sequential numeric column starting from 0 or 1.

What does the Group By transformation with the Sum operation on an Amount column produce?

Answer: One row per group with the total Amount

Group By aggregates rows into one summary row per unique group value.