Fix Power Query Merge Results Returning Blank or Missing Data
Question details
The user is experiencing blank values or missing equipment data when attempting to merge tables in Power Query.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Merging two data tables based on a common key (like Asset ID or Work Order ID) to retrieve equipment pool data.
- Observed behavior
- The merge operation completes successfully, but the resulting columns return blank or null values instead of the expected matching data.
Before modifying your queries, visually inspect a few known matching records in both tables to check for obvious differences like leading zeros, text case variations, or inconsistent formatting.
Clean Key Columns and Standardize Data Types
Remove hidden characters and align data types to ensure Power Query accurately matches the records from both tables.
The most common reason for missing data after a merge is that the key fields contain hidden trailing spaces, non-breaking spaces, or are formatted as different data types.
Select the key column in your first table. Go to the 'Transform' tab, click 'Format', and select 'Trim' to remove leading and trailing spaces.
Select the key column, click 'Replace Values' in the Transform tab. In the 'Value to Find' box, type #(00A0) and leave 'Replace With' empty to strip out non-breaking spaces.
Check the data type icon next to the column header. Ensure that both key columns in the respective tables are set to the exact same data type (e.g., both Text or both Whole Number).
Apply the exact same formatting, trimming, and replacing steps to the matching key column in your second table before attempting the merge.
Verify and Adjust the Join Kind Settings
Ensure you are using the correct join type based on how you want your data to combine.
Merge and Process Data Seamlessly with WPS Office
While Power Query is a feature specific to Microsoft Excel, WPS Spreadsheet offers powerful, lightweight, and completely free alternatives for merging datasets and cleaning data. Enjoy a familiar interface, intuitive data consolidation tools, and excellent compatibility with your existing workbooks.
Frequently Asked Questions
Why does Power Query say there are zero matches when merging?
This usually happens because of mismatched data types (for example, one column is formatted as Text and the other as Whole Number) or hidden whitespace characters like trailing spaces and non-breaking spaces preventing an exact match.
How do I remove non-breaking spaces in Power Query?
You can use the 'Replace Values' feature and search for the special character code #(00A0), or write a custom step in the Advanced Editor using Text.Replace([Column], Character.FromNumber(160), "").
Which Join Kind should I use to keep all my original data?
Use a 'Left Outer' join. This keeps all the rows from your primary (first) table and brings in matching data from the secondary table, leaving blank values only where no match exists.
Can case sensitivity cause blank merge results?
Yes, Power Query is case-sensitive by default. Text like 'AssetA' and 'asseta' will not match. You can fix this by transforming both key columns to UPPERCASE or lowercase before executing the merge.
