Query Editor interface in Excel - Dashboard Guide

Dashboard Mode - Query Editor interface

Goal

Understand how to use the Query Editor interface in Excel to clean and transform data before loading it into your spreadsheet.

Sample Data

Order ID	Customer	Product	Quantity	Price	Order Date
1001	John Doe	Apples	10	1.20	2024-01-15
1002	Jane Smith	Bananas	5	0.80	2024-01-16
1003	John Doe	Oranges	8	1.00	2024-01-17
1004	Mary Johnson	Apples	12	1.20	2024-01-18
1005	Jane Smith	Bananas	7	0.80	2024-01-19

Dashboard Components

• Data Preview Table: Shows the raw data loaded into Query Editor for cleaning and transformation.
• Remove Columns Step: Formula: = Table.RemoveColumns(Source, {"Order Date"}) - Removes the 'Order Date' column to focus on sales data.
• Filter Rows Step: Formula: = Table.SelectRows(RemoveColumns, each ([Quantity] > 5)) - Keeps only rows where quantity is greater than 5.
• Change Data Type Step: Formula: = Table.TransformColumnTypes(FilterRows, {{"Price", type number}}) - Ensures 'Price' column is treated as a number for calculations.
• Final Output Table: Displays the cleaned data after all transformations, ready to load into Excel.

Dashboard Layout

+----------------------+----------------------+
| Data Preview Table   | Remove Columns Step   |
| (Raw data)           | (Order Date removed)  |
+----------------------+----------------------+
| Filter Rows Step     | Change Data Type Step |
| (Quantity > 5)      | (Price as number)     |
+----------------------+----------------------+
|        Final Output Table (Cleaned Data)        |
+-------------------------------------------------+

Interactivity

Each step in the Query Editor builds on the previous one. When you change a step, all following steps update automatically. For example, if you change the filter to quantity > 7, the final output table updates to show only those rows.

Self Check

Add a filter step to keep only rows where Product is 'Apples'. Which components update?

• The Filter Rows Step updates to include the new product filter.
• The Change Data Type Step updates because it depends on the filtered data.
• The Final Output Table updates to show only 'Apples' orders with quantity > 5.
• The Data Preview Table and Remove Columns Step remain unchanged.

Key Result

Shows how to clean and transform sales data step-by-step using Excel's Query Editor interface.

Practice

1. What is the main purpose of the Query Editor interface in Excel?

easy

A. To protect worksheets with passwords

B. To write complex Excel formulas manually

C. To create charts and graphs from data

D. To clean and prepare data visually without writing formulas

Solution

1. Step 1: Understand Query Editor's role

   The Query Editor is designed to help users clean and prepare data visually.

2. Step 2: Compare options with Query Editor features

   Options B, C, and D describe other Excel features, not Query Editor's main purpose.

3. Final Answer:

   To clean and prepare data visually without writing formulas -> Option D

4. Quick Check:

   Query Editor = Visual data cleaning [OK]

Hint: Query Editor cleans data visually, no formulas needed [OK]

Common Mistakes:

• Confusing Query Editor with formula writing
• Thinking Query Editor creates charts
• Assuming Query Editor manages worksheet security

2. Which of the following actions can you perform directly in the Query Editor interface?

easy

A. Remove duplicate rows from a table

B. Write VBA macros to automate tasks

C. Insert pivot tables into the worksheet

D. Create conditional formatting rules

Solution

1. Step 1: Identify Query Editor capabilities

   Query Editor allows data cleaning tasks like removing duplicates.

2. Step 2: Eliminate unrelated options

   VBA macros, pivot tables, and conditional formatting are done outside Query Editor.

3. Final Answer:

   Remove duplicate rows from a table -> Option A

4. Quick Check:

   Query Editor = Remove duplicates [OK]

Hint: Removing duplicates is a common Query Editor task [OK]

Common Mistakes:

• Confusing Query Editor with VBA editor
• Thinking pivot tables are created in Query Editor
• Assuming formatting rules are set in Query Editor

3. You load a table into Query Editor and apply a filter to show only rows where the "Status" column equals "Complete". What will be the result after loading back to Excel?

medium

A. Only rows with "Status" = "Complete" are shown

B. The table is deleted from Excel

C. All rows remain, including those with other statuses

D. The filter is ignored and no changes happen

Solution

1. Step 1: Understand filtering in Query Editor

   Filtering in Query Editor limits data to matching rows.

2. Step 2: Effect of loading filtered data back

   Only filtered rows (Status = Complete) appear in Excel after loading.

3. Final Answer:

   Only rows with "Status" = "Complete" are shown -> Option A

4. Quick Check:

   Filter in Query Editor = Filtered data loaded [OK]

Hint: Filters in Query Editor limit data loaded back [OK]

Common Mistakes:

• Assuming all original rows remain
• Thinking filter deletes the table
• Believing filter is ignored on load

4. You tried to change a column's data type to "Date" in Query Editor but got an error. What is the most likely cause?

medium

A. You need to restart Excel before changing data types

B. You forgot to save the workbook before changing data type

C. The column contains text values that cannot convert to dates

D. Query Editor does not support changing data types

Solution

1. Step 1: Identify why data type change fails

   Changing to Date fails if values can't convert to dates.

2. Step 2: Rule out other options

   Saving workbook, restarting Excel, or Query Editor limitations are unrelated.

3. Final Answer:

   The column contains text values that cannot convert to dates -> Option C

4. Quick Check:

   Invalid data blocks type change [OK]

Hint: Check data values before changing type to Date [OK]

Common Mistakes:

• Blaming Excel save or restart
• Thinking Query Editor can't change types
• Ignoring invalid data values

5. You have two tables loaded into Query Editor: "Sales" and "Products". You want to combine them to show sales with product names. Which Query Editor feature should you use?

hard

A. Append Queries to stack tables vertically

B. Merge Queries to join tables on a common column

C. Filter Queries to remove unwanted rows

D. Group By to summarize sales totals

Solution

1. Step 1: Understand combining tables in Query Editor

   To join related data from two tables, use Merge Queries.

2. Step 2: Differentiate from other options

   Append stacks tables vertically, Filter removes rows, Group By summarizes data.

3. Final Answer:

   Merge Queries to join tables on a common column -> Option B

4. Quick Check:

   Combine tables by merging [OK]

Hint: Use Merge to join tables on matching columns [OK]

Common Mistakes:

• Using Append instead of Merge for joining
• Confusing Filter with combining tables
• Trying Group By to combine tables