Power Query interface overview in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a business analyst working with data in Power BI.

📋 Request: Your manager wants you to prepare a quick guide showing how to use the Power Query interface to clean and transform data.

📊 Data: You have a sample sales data table with columns: Date, Region, Product, Sales Amount, and Quantity.

🎯 Deliverable: Create a step-by-step guide that explains the main parts of the Power Query interface and how to perform basic data transformations.

Sample Data

Date	Region	Product	Sales Amount	Quantity
2024-01-01	North	Widget A	100	5
2024-01-02	South	Widget B	150	3
2024-01-03	East	Widget A	200	7
2024-01-04	West	Widget C	120	4
2024-01-05	North	Widget B	180	6
2024-01-06	South	Widget C	130	2
2024-01-07	East	Widget B	170	5
2024-01-08	West	Widget A	160	3

1

Step 1: Open Power BI Desktop and load the sample sales data into Power Query Editor by selecting 'Transform Data'.

No formula needed; just load data.

Expected Result

Power Query Editor opens showing the sales data table.

2

Step 2: Identify the main parts of the Power Query interface: Ribbon, Queries pane, Data preview, and Applied Steps pane.

No formula; visually locate these areas.

Expected Result

You see the Ribbon at the top, Queries pane on the left, Data preview in the center, and Applied Steps on the right.

3

Step 3: Use the Ribbon to perform a basic transformation: remove the 'Quantity' column by selecting it and clicking 'Remove Columns'.

No formula; use the 'Remove Columns' button.

Expected Result

'Quantity' column is removed from the data preview and a new step is added in Applied Steps.

4

Step 4: Rename the 'Sales Amount' column to 'Revenue' by right-clicking the column header and choosing 'Rename'.

No formula; rename manually.

Expected Result

Column header changes to 'Revenue' and Applied Steps records this change.

5

Step 5: Filter the data to show only rows where Region is 'North' by clicking the filter icon on the Region column and selecting 'North'.

No formula; use filter dropdown.

Expected Result

Data preview shows only rows with Region = 'North' and Applied Steps logs the filter.

6

Step 6: Check the Applied Steps pane to see all transformations applied in order: Source, Removed Columns, Renamed Columns, Filtered Rows.

No formula; review Applied Steps.

Expected Result

You see a list of steps showing each transformation applied to the data.

7

Step 7: Close and apply the changes to load the transformed data back into Power BI by clicking 'Close & Apply'.

No formula; click button.

Expected Result

Power BI loads the cleaned data for further analysis.

Final Result

Power Query Interface Overview

+----------------------+---------------------+---------------------+
| Ribbon               | Queries Pane        | Applied Steps Pane  |
| (Buttons for         | (List of queries)   | (List of steps)     |
| transformations)     |                     |                     |
+----------------------+---------------------+---------------------+
|                      Data Preview (shows your data)                  |
+---------------------------------------------------------------------+

Steps performed:
- Removed 'Quantity' column
- Renamed 'Sales Amount' to 'Revenue'
- Filtered Region to 'North'

Data ready for analysis in Power BI.

✓Power Query interface has four main parts: Ribbon, Queries pane, Data preview, and Applied Steps pane.

✓Applied Steps pane tracks every change made to the data in order.

✓Basic transformations like removing columns, renaming, and filtering are easy to perform using the Ribbon and column menus.

✓After cleaning data in Power Query, you must 'Close & Apply' to use the data in Power BI reports.

Bonus Challenge

Try adding a new column that calculates 'Revenue per Quantity' by dividing 'Sales Amount' by 'Quantity' before removing the 'Quantity' column.

Show Hint

Use the 'Add Column' tab in the Ribbon and select 'Custom Column'. Enter the formula: [Sales Amount] / [Quantity].