How to Include Blank Values When Merging Queries in Power Query
Question details
The user needs to merge datasets in Power Query without dropping records that contain blank matching values in key columns.
- Product
- Power Query
- Device & OS
- not provided
- Scenario
- Merging multiple queries or tables where the matching identifier columns (such as account numbers or profit centers) contain blank or null values.
- Observed behavior
- Power Query drops or ignores rows with blank or null values during the merge process because it cannot match a blank value from one table to a blank value in another.
Before modifying your queries, create a backup copy of your original dataset and identify the exact columns containing the blank values that will be used as matching keys.
Use Placeholder Values During the Query Merge
Replace blank or null values with a consistent placeholder identifier before the merge, and revert them back to blanks after the data is successfully combined.
Because Power Query cannot equate two null or blank values during an inner merge, assigning them a temporary, identical value forces the merge logic to recognize them as a match. This is a common practice in large system conversions.
Open the Power Query Editor and locate the columns you intend to use for the merge (e.g., Account Number or Profit Center) that contain the problematic blank values.
Right-click the column header and select 'Replace Values'. In the prompt, enter null (or leave blank) for 'Value To Find', and type a unique placeholder like '999999' or '888888' in the 'Replace With' field. Repeat this for both tables.
Navigate to the Home tab, click 'Merge Queries', and select the matching columns that now contain your temporary placeholders. The rows will now merge successfully instead of being skipped.
After expanding your merged table, select the key columns again, right-click, and choose 'Replace Values'. Enter your placeholder (e.g., '999999') in 'Value To Find' and leave 'Replace With' empty or type null to restore the original blank state.
Simplify Data Merging with WPS Office Spreadsheet
If complex Power Query setups and data merge issues are slowing down your workflow, WPS Spreadsheet offers an intuitive and reliable alternative. Handle large datasets, perform quick lookups, and merge tables effortlessly without advanced query configuration.
- 1. Open Your Spreadsheet: Launch WPS Office Free and open your existing data workbooks.
- 2. Use Advanced Lookup Functions: Use VLOOKUP or INDEX-MATCH functions to merge data flexibly without dropping rows containing blanks.
- 3. Consolidate Data Easily: Utilize the 'Consolidate' tool under the Data tab to quickly summarize and merge information from multiple sheets.
Frequently Asked Questions
Why does Power Query ignore null values during an inner merge?
By default, Power Query interprets 'null' or blank fields as an absence of data rather than a distinct value. Consequently, it refuses to equate a null in Table A with a null in Table B, meaning those rows fail the matching condition and are dropped from the inner merge results.
Can I use a Left Outer Merge instead of replacing values?
Yes, using a Left Outer Merge will retain all rows from your primary table, including those with blank key values. However, if the secondary table also relies on that blank key to bring over corresponding data, the joined fields will simply return null for those rows.
How do I ensure empty strings are treated as nulls before replacing?
Empty strings ("") and true nulls are treated differently in Power Query. Before applying your placeholder, select the column, go to the Transform tab, click 'Replace Values', and replace empty values with 'null' (typed without quotes). Then, apply the numerical placeholder to the nulls.
