Column Arithmetic &
Formula Bar Renaming
Compute derived columns by multiplying sales by margin percentages, learn to rename a column directly in the formula bar to avoid extra steps, then aggregate and group the results by month.
Power Query can perform arithmetic between columns directly — multiplying Sales by Margin % to produce Margin Dollars, for example — using Add Column → Standard. Each operation creates a new column and a new Applied Step. By default, Power Query names these steps generically (e.g. "Inserted Multiplication"), and you'd normally rename the column in a separate step.
This lab introduces a cleaner technique: edit the formula bar of the step that created the column and change the column name there, eliminating the need for a separate rename step. Keeping your Applied Steps list lean makes queries faster to read and easier to maintain. You'll repeat this pattern for three managers, then aggregate and group — reinforcing the Group By workflow from Lab 07.
| # | Steps |
|---|---|
| 1 | New query for File 3Z — extract the DailyManagerSalesAndMargin table. |
| 2 | Create a new column to multiply Manager 1 Sales by Manager 1 Margin. |
| 3 |
Instead of renaming the new column in a separate step, change its name directly in the formula bar of the Insert Multiplication step.
Change the name to M1 Margin Dollars. In the formula bar, find the default column name in the M formula and replace it.
|
| 4 | Repeat steps 2 & 3 for Manager 2 — use M2 Margin Dollars as the column name. |
| 5 | Repeat steps 2 & 3 for Manager 3 — use M3 Margin Dollars as the column name. |
| 6 | Delete the three margin percentage columns. |
| 7 | Calculate Total Sales in a new column using the sum function. |
| 8 | Calculate Total Margin in a new column using the sum function. |
| 9 | Delete all manager columns. |
| 10 | Transform the Transaction Date column by identifying the start of the month of each date. |
| 11 | Use the Group By function to group by transaction date. |