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.