How to Extract Rows Between Marker Words in Power Query
Question details
The user needs to extract specific rows of data located between changing textual markers in Power Query, where the row count varies daily.
- Product
- Power Query
- Device & OS
- not provided
- Scenario
- Separating and organizing data blocks that are bound by specific keywords (markers) into distinct groupings.
- Observed behavior
- Data is imported as a single continuous list, and the goal is to output separate data tables for the entries falling between each specific marker.
Ensure your dataset is successfully loaded into the Power Query Editor and precisely identify your marker words, as Power Query operations are strictly case-sensitive.
Use a Conditional Column and Fill Down to Group Data
This method effectively groups rows into specific sections by identifying marker words, filling the identifiers down, and separating them into different queries.
By creating a custom conditional column, you assign a unique numerical identifier to each specific marker. Using the 'Fill Down' feature ensures that all varying rows beneath a marker inherit that specific identifier until the next marker is reached.
Navigate to the 'Add Column' tab in the Power Query ribbon, click 'Custom Column', and enter a conditional formula such as: = if Text.Lower([Column1]) = "jumbo" then 1 else if Text.Lower([Column1]) = "kibble" then 2 else null
Select the newly created custom column, go to the 'Transform' tab, click on the 'Fill' dropdown, and select 'Down'. This applies the group number to all the standard rows below each marker.
Create referenced queries for each section by right-clicking the main query in the Queries pane and selecting 'Reference'. In each new query, click the filter dropdown on your custom column and select a specific group number (e.g., 1 for Jumbo).
Filter out the actual marker rows and any null values from the original data column. Finally, right-click the custom identifier column and select 'Remove' to clean up your final table.
Tag and Filter Out Unwanted Marker Rows directly
If your primary goal is to simply remove the marker words and consolidate all remaining data together, you can flag the markers and filter them out in a single query.
Manage Your Spreadsheets with WPS Office
While Power Query requires specific advanced operations, WPS Office provides a lightweight, fast, and highly capable alternative for your daily spreadsheet and data analysis needs. Enjoy exceptional compatibility with major file formats and a familiar interface without the expensive subscription fees.
- 1. Download and Install: Download WPS Office for free from the official website and follow the easy installation prompts.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel or CSV files directly.
- 3. Process Your Data: Utilize advanced AutoFilter, Text to Columns, and Pivot Tables to clean and manage your data blocks efficiently.
Frequently Asked Questions
Why is my Power Query custom column returning errors for my marker words?
Power Query is strictly case-sensitive. If your formula checks for 'jumbo' but the text in the cell is 'Jumbo', it will not register a match. Wrapping your column reference in the Text.Lower() function ensures case mismatches are ignored.
How does Power Query handle the varying number of rows between markers each day?
The Fill Down function automatically handles dynamic row counts. It systematically copies the custom marker identifier to every row below it until it encounters the next non-null value, regardless of whether there are 5 or 500 rows.
Can I extract data between multiple different markers in the same dataset?
Yes. By nesting multiple 'else if' statements within your custom column formula for each distinct marker word, you can accurately categorize the entire dataset. You can then reference the main query multiple times to filter out each unique category.
