7.18 Keep / Remove Rows, Sort and Filter

By Ravindra Bagale Last updated: 25 September 2026

Command (Home tab)	What it does	Quick-commerce example
Keep Rows › Keep Top Rows	First N rows	Keep the top 100 rows while testing a big file
Keep Rows › Keep Bottom Rows	Last N rows	Latest 50 rows of a log sorted by time
Keep Rows › Keep Range of Rows	From row X, N rows	Rows 11–60 of a report body
Keep Rows › Keep Duplicates / Keep Errors	Rows with duplicate values or errors	Investigation queries
Remove Rows › Remove Top / Bottom Rows	Drops title or footer rows	Remove 3 title rows (7.3)
Remove Rows › Remove Alternate Rows	Removes a repeating pattern	A PDF-to-Excel export with a blank line after every order
Remove Rows › Remove Duplicates / Blank Rows / Errors	Cleaning	See 7.4, 7.5, 7.15
Sort Ascending / Descending	Orders rows	Sort by Order DateTime
Column filter arrow	Text/Number/Date filters	Date Filters › In the Previous › 12 Months; Text Filters › Does Not Contain "TEST"

Remove Alternate Rows asks for First row to remove, Number of rows to remove and Number of rows to keep. For "order line, blank line, order line, blank line …" use 2, 1, 1.

Top100    = Table.FirstN(Source, 100),
RangeRows = Table.Range(Source, 10, 50),          // skip 10, keep 50
Alternate = Table.AlternateRows(Source, 1, 1, 1),  // offset, skip, take
NoTests   = Table.SelectRows(Source, each not Text.Contains([Customer Name], "TEST"))

Ravindra Bagale's Tip

Friends, remember: keeping Top Rows to make the editor faster and then forgetting to remove the step is a classic mistake: the model loads only 100 rows and every total is wrong. Use a parameter such as DevMode for development filtering, or delete the step before publishing. Check the row count in the model after loading. Don't worry – after doing it two or three times, it becomes a habit.

Ravindra Bagale's Tip – मराठी

मित्रांनो, लक्षात ठेवा: editor जलद व्हावा म्हणून Top Rows ठेवणं आणि मग तो step काढायला विसरणं ही classic चूक आहे: model फक्त 100 rows load करतं आणि प्रत्येक total चुकतो. Development filtering साठी DevMode सारखा parameter वापरा, किंवा publish करण्याआधी तो step delete करा. Load केल्यावर model मधला row count तपासा. घाबरू नका, दोन-तीन वेळा केलं की सवय होते.

Ravindra Bagale's Tip – हिंदी

दोस्तों, याद रखो: editor तेज़ करने के लिए Top Rows रखना और फिर उस step को हटाना भूल जाना classic गलती है: model सिर्फ़ 100 rows load करता है और हर total गलत होता है. Development filtering के लिए DevMode जैसा parameter इस्तेमाल करो, या publish करने से पहले वह step delete करो. Load के बाद model में row count check करो. घबराओ मत, दो-तीन बार करने पर आदत हो जाती है.

Practice task

Filter the Orders query to remove test orders (Customer Name contains "TEST") and orders before 01-01-2025. Sort by Order DateTime descending and keep the top 20 rows in a duplicate query for a quick check.