How to Add Dates from One Table to Another in Power Query
Question details
The user needs to pull specific invoice dates from an Invoice Detail table into a Miscellaneous Charge table based on multiple matching columns, without appending the data or increasing the row count.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Merging data between two tables in Power Query using shared matching criteria to extract a single column of information.
- Observed behavior
- The user requires a structured method to accurately look up and append date fields to existing rows without unintentionally adding new records to the dataset.
Ensure both your primary table and the reference table containing the dates are properly loaded into the Power Query Editor before initiating the merge operation.
Merge Queries with a Left Outer Join
Use Power Query's 'Merge Queries' feature to match multiple shared columns and extract the specific Date column into your main table.
By utilizing a Left Outer Join, you can align data exactly like an advanced VLOOKUP. This method allows you to match multiple conditions simultaneously and fetch only the desired columns without appending tables or creating new rows.
Open the Power Query Editor, navigate to the Queries pane on the left, and click on your primary table (e.g., EM - Misc Charge). Then, go to the 'Home' tab and click 'Merge Queries'.
In the Merge dialog box, select your secondary table (e.g., Invoice Detail) from the drop-down menu provided below your primary table preview.
Hold down the 'Ctrl' key on your keyboard and click the shared columns in both tables in the exact same sequence (e.g., DBName-Point_Id, DBName-Id, and Charge). Small numbers will appear in the column headers to indicate the matching order.
Under the 'Join Kind' dropdown, select 'Left Outer (all from first, matching from second)'. Click 'OK' to process the merge.
A new column containing table data will appear at the end of your primary table. Click the expand icon (two diverging arrows) on the column header, select only the 'Date' column, uncheck 'Use original column name as prefix', and click 'OK'.
Looking for a Lightweight Alternative for Data Processing?
If you are struggling with heavy Excel add-ins like Power Query, WPS Office offers a completely free, highly compatible, and user-friendly spreadsheet application. You can easily handle complex data merges using advanced multi-condition formulas like XLOOKUP or INDEX/MATCH natively.
Frequently Asked Questions
Why did my row count increase after merging queries?
Your row count will increase if the secondary table contains duplicate rows for the matching keys. Ensure the secondary table has unique combinations of your matched columns before merging, or use 'Remove Duplicates' on the reference table beforehand.
Can I match multiple columns at once in a Power Query merge?
Yes. By holding down the Ctrl key, you can select multiple columns in the Merge Queries dialog box. It is crucial that you select them in the exact same order for both the top and bottom tables so the matching logic aligns correctly.
What is the difference between Merge and Append in Power Query?
Merging adds columns from one table to another based on shared matching keys (similar to a database join or VLOOKUP). Appending stacks the rows of one table on top of or below another table to create a longer, continuous list.
