6.3 The Power Query Ribbon at a Glance
Start with a problem
Ruhi trims the Store column but loses the original value she needed for an audit. She used the wrong tab.
Explain simply
Many commands are on two tabs. Transform changes the existing column. Add Column keeps the original and creates a new column.
Show the data
Before:
| Store |
|---|
| "Kothrud " |
| baner |
| KOTHRUD |
| Baner |
About this data: This table uses columns: Store, Trimmed Text. This table uses columns: Tab used, Columns after the step. This table uses columns: Tab, Main groups and commands. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: pq_raw_pune_export.csv (source: src/practice/).
Demonstrate
-
Select Store › Transform › Format › Trim.
Your result should look like this:
Store Kothrud baner KOTHRUD Baner -
Delete that step (✕) to undo.
-
Select Store › Add Column › Format › Trim.
Your result should look like this:
Store Trimmed Text "Kothrud " Kothrud baner baner KOTHRUD KOTHRUD Baner Baner -
Right-click a column header to see the most common commands without the ribbon.
Show the result
Your result should look like this (fictional sample):
| Tab used | Columns after the step |
|---|---|
| Transform | Store (changed in place) |
| Add Column | Store (original) + Trimmed Text (new) |
Explain what happened
Transform rewrote the column with Table.TransformColumns; Add Column used Table.AddColumn and kept the original.
Explore further
- Home tab repeats the most-used commands (Remove Rows, Group By, Data Type).
Detailed reference for this lesson:
| Tab | Main groups and commands |
|---|---|
| Home | Close & Apply, New Source, Recent Sources, Enter Data, Data source settings, Manage Parameters, Refresh Preview, Advanced Editor, Choose/Remove Columns, Keep/Remove Rows, Sort, Split Column, Group By, Data Type, Use First Row as Headers, Replace Values, Merge Queries, Append Queries, Combine Files |
| Transform | Table: Group By, Use First Row as Headers, Transpose, Reverse Rows, Count Rows · Any Column: Data Type, Detect Data Type, Rename, Replace Values, Fill, Pivot Column, Move, Unpivot Columns · Text Column: Split Column, Format, Merge Columns, Extract, Parse · Number Column: Statistics, Standard, Scientific, Trigonometry, Rounding, Information · Date & Time Column: Date, Time, Duration |
| Add Column | General: Column From Examples, Custom Column, Invoke Custom Function, Conditional Column, Index Column, Duplicate Column · From Text · From Number · From Date & Time (full coverage in Module 8) |
| View | Query Settings, Formula Bar, Monospaced, Show whitespace, Column quality, Column distribution, Column profile, Go to Column, Advanced Editor, Query Dependencies |
| Tools / Help | Diagnostics (query diagnostics), help links |
Transform tab vs Add Column tab
Lakshat theva, many commands appear on both tabs. On the Transform tab they change the existing column. On the Add Column tab they create a new column and keep the original. Choose carefully.
Ravindra Bagale's Tip
Friends, many students hunt through the ribbon for every transformation. Right-click the column header, or use the small table icon at the top-left of the data grid. Most common transformations are one click away there. Don't make this mistake!
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students प्रत्येक transformation साठी ribbon मध्ये शोधत बसतात. Column header वर right-click करा, किंवा data grid च्या top-left ला असलेला छोटा table icon वापरा. बहुतेक common transformations तिथे एका click वर मिळतात. ही चूक करू नका!
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students हर transformation के लिए ribbon में ढूँढते रहते हैं. Column header पर right-click करो, या data grid के top-left में छोटा table icon इस्तेमाल करो. ज़्यादातर common transformations वहाँ एक click पर मिल जाते हैं. यह गलती मत करना!
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Original value lost | Used Transform | Use Add Column when you need both |
| Two near-identical columns in the model | Used Add Column and never removed the old one | Remove or hide the extra column |
| Cannot find a command | Searching only the ribbon | Right-click the header or use the table icon at top-left of the grid |
Practise independently
Practice task
You need both the raw and the upper-case Order ID for an audit. Which tab?
Hints
- Keep the original.
Solution
Add Column › Format › UPPERCASE – it keeps Order ID and adds a new upper-case column.
Think like a professional
Choosing the right tab keeps the model lean and the audit trail intact.
Revision card
Comparison card