How to Use Power Query to Filter and Copy Data Based on Two Columns
Question details
The user needs to use Power Query to filter rows by a specific level, match values between two columns (such as Number and ParentNumber), and output the selected data into a new worksheet that automatically updates when the source CSV is replaced.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the process of extracting, filtering, and merging CSV data based on hierarchical or relational columns.
- Observed behavior
- The user wants a streamlined query that successfully matches rows using an Inner Join, cleans up unwanted columns, and remains fully refreshable.
Ensure your source CSV file is saved in a stable, accessible folder. Moving or renaming this file later will break the Power Query connection unless you update the data source settings.
Filter and Merge Columns using Power Query
Import the CSV, filter the base criteria, and merge the query with the original table using an Inner Join to extract matched rows.
This method involves loading the CSV data twice (or referencing the initial query) so you can compare the base level data against the parent-child relational columns.
Using an Inner Join ensures that only rows with exact matches across both columns are kept in the final output.
Navigate to the Data tab on your ribbon. Click 'Get Data' > 'From File' > 'From Text/CSV'. Select your source file and click 'Import', then select 'Transform Data' to open the Power Query Editor.
In the Power Query Editor, filter the 'Level' column to display only 'Level 0' values. Right-click this query in the left Queries pane and choose 'Reference'. This creates a new query linked to your filtered data.
With the new query selected, go to the Home tab and click 'Merge Queries'. In the dialog box, select the original source query from the dropdown. Click the 'Number' column in the top table, and the 'ParentNumber' column in the bottom table.
Under 'Join Kind', select 'Inner (only matching rows)' and click OK. Click the expand icon (two arrows) at the top of the newly created merged column. Choose the specific fields you want to load into your final sheet.
Remove any unnecessary columns by right-clicking their headers and selecting 'Remove'. Finally, click 'Close & Load' on the Home tab to export the matched data into a new worksheet.
Need a Fast, Lightweight Tool for Daily Data Tasks?
While complex Power Query integrations are native to Microsoft Excel, WPS Office Spreadsheets offers an incredibly fast and lightweight alternative for standard data filtering, PivotTables, and day-to-day spreadsheet management without a hefty subscription fee.
Frequently Asked Questions
Will the Power Query output update automatically if I modify the original CSV file?
Yes. If the modified CSV is saved in the exact same location with the same file name, you can simply click 'Refresh All' on the Data tab to automatically update your output worksheet.
Why do I get errors when merging the Output Data query?
Errors typically occur if the column headers in the source file have changed, or if there is a data type mismatch between the 'Number' and 'ParentNumber' columns. Ensure both columns are formatted identically in the Power Query Editor (e.g., both as Text or both as Whole Numbers).
What is the difference between an Inner Join and a Left Outer Join in Power Query?
An Inner Join returns only the rows where there is a match in both specified columns (like Number and ParentNumber). A Left Outer Join keeps all rows from your first table regardless of whether a match exists in the second table.
