8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns
8.6 Index Column
Start with a problem
After sorting and removing rows, Amir cannot tell which source row an error came from.
Explain simply
Index Column adds a running number (from 0, from 1 or custom). Add it right after Source to keep the original row order.
Show the data
| Order ID | Amount |
|---|---|
| 201 | 500 |
| 202 | 300 |
| 203 | 700 |
| 204 | 400 |
| 205 | 0 |
About this data: This table uses columns: Order ID, Amount. This table uses columns: Order ID, Amount, Source Row. This table uses columns: Use, Example. 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: column_vs_measure_orders.csv (source: src/practice/).
Demonstrate
-
Right after Promoted Headers, Add Column › Index Column › From 1.
Your result should look like this:
Index 1, 2, 3, 4, 5 -
Sort Amount descending.
Your result should look like this:
Order (Index) 203 (3), 201 (1), 204 (4), 202 (2), 205 (5) -
Rename Index to
Source Row.
Show the result
Your result should look like this (fictional sample):
| Order ID | Amount | Source Row |
|---|---|---|
| 203 | 700 | 3 |
| 201 | 500 | 1 |
| 204 | 400 | 4 |
Explain what happened
The index was assigned before sorting, so it still points to the original file row.
Explore further
- Index also works as a tie-breaker for ranking.
Detailed reference for this lesson:
Add Column › Index Column › From 0 / From 1 / Custom… (Custom lets you choose the Starting index and Increment, e.g. start 1001, step 1).
| Use | Example |
|---|---|
| Row number for tracing | Row 57 of the Pune file has a bad date |
| Surrogate key | Create a Store Key for a store list that has no ID |
| Previous-row comparisons | Add Index From 0 and From 1, then merge the table with itself to compare each order with the previous order of the same rider |
| Sort order | Keep the original order of a festival list |
Ravindra Bagale's Tip
Friends, pay attention: an index added after sorting or filtering changes whenever the data changes, so don't use it as a permanent key. Add the index where the order is meaningful. Remember, adding an index usually stops query folding, so put it as late as possible. Try it once more, then move on.
Ravindra Bagale's Tip – मराठी
मित्रांनो, लक्ष द्या: sort किंवा filter केल्यानंतर जोडलेला index data बदलला की बदलतो, म्हणून तो कायमची key म्हणून वापरू नका. जिथे order ला अर्थ आहे तिथे index जोडा. लक्षात ठेवा, index जोडल्याने साधारणपणे query folding थांबतं, म्हणून तो शक्य तितक्या उशिरा ठेवा. पुन्हा एकदा करून बघा, मग पुढे जा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, ध्यान दो: sort या filter के बाद जोड़ा गया index data बदलते ही बदल जाता है, इसलिए उसे स्थायी key मत बनाओ. Index वहाँ जोड़ो जहाँ order का मतलब हो. याद रखो, index जोड़ने से आमतौर पर query folding रुक जाता है, इसलिए उसे जितना हो सके बाद में रखो. एक बार फिर करके देखो, फिर आगे बढ़ो.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Index follows sorted order | Added after sort | Add it first |
| Starts at 0 | Chose From 0 | From 1 for humans |
| Different numbers after refresh | Source order changed | Sort source by a stable key first |
Practise independently
Practice task
Which Source Row is order 205?
Hints
- Fifth line.
Solution
5.
Think like a professional
Traceability to source rows speeds up data-quality conversations.