How to Perform a Case-Insensitive Merge in Power Query on Mac
Question details
The user needs to merge Power Query tables based on text columns with inconsistent capitalization without relying on fuzzy matching.
- Product
- Excel for Mac
- Device & OS
- Mac
- Scenario
- Merging two data tables where the matching text columns have mismatched uppercase and lowercase letters.
- Observed behavior
- Standard merging fails due to case sensitivity, and the fuzzy matching feature is unavailable in Excel for Mac.
Ensure both tables you intend to merge are loaded into the Power Query Editor and identify the specific columns containing the text values you want to use as your matching criteria.
Normalize Text Case Before Merging
Transform the capitalization of the matching columns in both tables to be identical, allowing for a standard exact-match merge.
Since fuzzy matching is not supported on Excel for Mac, the most reliable workaround is to standardize the text format in your query. By forcing all text in the matching columns to be strictly lowercase, uppercase, or capitalized, Power Query can successfully perform an exact match.
Launch the Power Query Editor and select your first table. Click on the header of the column you plan to use for the merge to highlight it.
Navigate to the 'Transform' tab on the top ribbon, click on 'Format', and select 'Lowercase' (or 'Uppercase'). This will convert all text in that column to the same case.
Select your second table in the Queries pane. Highlight its matching column, go to the 'Transform' tab, and apply the exact same formatting step (e.g., 'Lowercase') you used for the first table.
With both columns now sharing the exact same capitalization logic, go to the 'Home' tab, select 'Merge Queries', choose the normalized columns from both tables, and execute the merge without needing fuzzy matching.
Try WPS Office for Seamless Data Management on Mac
If you are struggling with complex or missing features in Excel for Mac, WPS Office provides a robust, lightweight, and completely free alternative. It offers an intuitive interface with highly compatible data analysis tools designed to run flawlessly on macOS.
Frequently Asked Questions
Why is the fuzzy matching option missing in Power Query on my Mac?
Fuzzy matching relies on specific underlying Microsoft Windows algorithms and components. Currently, these components are not fully integrated or supported in the macOS version of Excel, which is why the feature is absent.
Will changing the case in Power Query alter the text in my final exported report?
Yes, if you load the merged data directly to a worksheet, the matching column will display the transformed case (e.g., all lowercase). If you need the original case for presentation, keep a duplicate of the original column before applying the lowercase transformation, and use the duplicated column solely for the merge.
Can I use 'Capitalize Each Word' instead of 'Lowercase'?
Absolutely. You can choose Lowercase, Uppercase, or Capitalize Each Word from the Format menu. The only strict requirement is that you must apply the exact same transformation to the matching columns in both tables so they align perfectly during the merge.
