'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Merge Data from Multiple Excel Sheets by Product ID Using Power Query
Question details
The user needs to consolidate product information stored across multiple Excel worksheets into a single, organized sheet using Product ID as the common matching key.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining fragmented data about products, including multiple related batch numbers (Metrc Tags), into one organized master sheet without simply stacking the tables.
- Observed behavior
- Data is currently separated into multiple worksheets, requiring a specialized join operation to align the information correctly horizontally by Product ID rather than appending it vertically.
Ensure all your source data ranges are formatted as Excel Tables (Ctrl+T) and that the 'Product ID' column shares the exact same name and formatting type across all worksheets.
Use Power Query to Merge Tables by Product ID
Utilize Excel's Power Query feature to perform a Left Outer Join, which will match and combine columns based on the Product ID while handling multiple related batch numbers.
Power Query allows you to link tables together dynamically. By using the Merge Queries feature, you can align scattered product data into a single comprehensive table.
Unlike standard copy-pasting or the Append function, a Merge aligns data horizontally based on a matching key. This ensures that every attribute, including multiple batch numbers or tags, accurately corresponds to the correct Product ID.
Select your first Excel Table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor. Repeat this step to load all relevant tables.
In the Power Query Editor, verify that the 'Product ID' column is set to the same Data Type (e.g., Text) in all queries. Use the Trim tool under the Transform tab to remove any hidden spaces that might prevent a match.
Select your main table query on the left sidebar. Go to Home > Merge Queries. In the dialog box, select your second table from the dropdown. Click the 'Product ID' column in both preview windows, select 'Left Outer' as the Join Kind, and click OK.
Click the small double-arrow expand icon on the newly merged column's header. Uncheck 'Use original column name as prefix', select the data columns you wish to bring over (like batch numbers), and click OK.
If a Product ID has multiple batch numbers (Metrc Tags), select the Product ID column, click 'Group By' under the Transform tab, and create a new column. You can then adjust the formula bar to use the Text.Combine function to format the tags as a comma-separated list.
Once your data is fully combined and cleaned, click the Home tab, then select 'Close & Load'. This will output your newly consolidated data into a brand new Excel worksheet.
Seamlessly Manage and Analyze Spreadsheets with WPS Office
While advanced M-code setups are specific to Microsoft Excel's Power Query, WPS Office provides a lightweight, free alternative with robust built-in data consolidation tools and excellent format compatibility for your daily spreadsheet tasks.
- 1. Download and Install: Get WPS Office for free from the official website and easily install it on your PC or Mac.
- 2. Open Existing Excel Files: Directly open your existing .xlsx workbooks; WPS Spreadsheet supports seamless file reading and data formatting.
- 3. Consolidate Data Easily: Use the built-in 'Consolidate' feature under the Data tab to summarize and combine ranges across sheets without writing complex queries.
Frequently Asked Questions
Why aren't my Product IDs matching correctly in Power Query?
This usually happens due to formatting mismatches. If one table stores the Product ID as Text and another as a Whole Number, Power Query won't recognize them as a match. Additionally, leading or trailing spaces can break the merge. Highlight the column, change the Data Type so they match, and use Transform > Format > Trim to remove unseen spaces.
What is the difference between Merge and Append in Power Query?
Appending queries stacks tables vertically, adding rows to the bottom of your data set. Merging queries joins tables horizontally, adding columns side-by-side based on a shared matching column (like Product ID). Since you need to consolidate different product details together by an ID, Merging is the correct tool.
Can I merge more than two worksheets at once in Power Query?
Power Query's standard Merge Queries dialog only allows you to join two tables at a time. To merge data from three or more worksheets, you must first merge the first two tables, and then merge the third table into the resulting merged query, repeating this process until all tables are combined.
How do I retain multiple batch numbers for one Product ID in my merged sheet?
When you merge tables where one Product ID has multiple batch numbers, Power Query will duplicate the Product ID row for each batch number. To clean this up, use the 'Group By' feature on the Product ID, and then manually edit the generated M-code in the formula bar to use the 'Text.Combine' function, which combines the batch numbers into a single comma-separated text string within one cell.
