How to Return Rows After the Last 8 in Power Query
Question details
The user wants to extract all rows that appear after the final occurrence of the number 8 in a specific column of a Power Query table.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Filtering and transforming a dataset based on the dynamic position of a specific target value.
- Observed behavior
- The user needs a method to isolate rows following the last instance of the target number, preferably using the standard graphical interface rather than relying solely on custom M code.
Ensure your data is properly formatted as a table and loaded into the Power Query Editor before applying these transformation steps.
Extract Rows Using the Power Query User Interface
A step-by-step method using standard Power Query interface buttons like Index, Conditional Columns, and Grouping.
This method avoids custom programming by utilizing helper columns to mark the target value and group the data accordingly.
Navigate to the 'Add Column' tab in the Power Query ribbon and click 'Index Column' to create a unique identifier for each row.
Click 'Conditional Column' under the Add Column tab. Set a rule: if your target column (e.g., 'Number') equals 8, then output the value from the 'Index' column.
Select your newly created conditional column, go to the 'Transform' tab, click 'Fill', and choose 'Down'. This assigns the index of the number 8 to all subsequent rows until the next 8 appears.
Group the table by this conditional column without aggregating. Then, sort the grouped results in descending order and keep only the first row, which represents the final cluster of data.
Drill down into the remaining table data. Filter out the top row where the Number actually equals 8, and remove all temporary helper columns to leave only your desired dataset.
Extract Rows Using Custom M Code
A faster, single-step approach for advanced users comfortable with modifying formulas in the Advanced Editor.
Handle Your Spreadsheets Efficiently with WPS Office
While Power Query offers specific data modeling functions in Microsoft Excel, WPS Office provides a lightweight, highly compatible, and free alternative for your daily spreadsheet processing. Easily filter, sort, and analyze your data without heavy processing overhead.
- 1. Download the Installer: Visit the official WPS Office website to download the free installation package for your operating system.
- 2. Install WPS Office: Run the downloaded file and follow the simple on-screen instructions to complete the setup in minutes.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel workbooks to seamlessly continue your data filtering and analysis.
Frequently Asked Questions
Can I extract rows after a different text or number value in Power Query?
Yes, you can easily replace the number 8 in both the Conditional Column setup or the M code formula with any other number, text string, or date you need to use as your reference point.
Why use an Index column for the user interface method?
The Index column provides a unique row identifier. When combined with a Conditional Column and the Fill Down tool, it creates a unique grouping ID for each section of data separated by your target value.
What happens if the target value does not exist in the dataset?
If using the UI method, the grouping step may fail to segment the data as expected. With the M code List.PositionOf function, it will return an error or null if the value isn't found, which is why it is recommended to ensure the value exists or wrap the step in error-handling logic.
Is M Code faster than the user interface method for this task?
Generally, yes. A single M code function like Table.RemoveFirstN is more computationally efficient and requires fewer processing steps than adding multiple helper columns and grouping data, resulting in faster query refresh times.
