Case sensitivity in Power Query: the silent cause of missing matches
Troubleshooting · By Tahir Nas · Examples run in our simulator · Updated
9 min read
Power Query compares text exactly, so "Leeds" and "leeds" are different values and a merge, filter or Remove Duplicates step treats them as different without any warning. The reliable fix for a merge is to build a lower-case, trimmed key column on both sides and join on that; for filters, duplicates and groups, Text.Contains, List.Contains, Table.Distinct and Table.Group accept Comparer.OrdinalIgnoreCase as an option.
One comparison, two answers
Text equality in M is case-sensitive. The smallest possible test:
let
Result = "Leeds" = "leeds"
in
ResultResult
false
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The answer is false. Microsoft's guide to duplicates puts it plainly: Power Query is case-sensitive, and it considers the case of the text when working with duplicate values (Working with duplicate values, Microsoft Learn). Every step that compares text inherits this, which is why case problems rarely look like case problems. They look like missing lookups, a row count that's too high after deduplication, or a total split across two groups.
Merges that miss
A merge compares key values for equality, so a customer email typed with capitals on one side doesn't match the lower-case version on the other:
let
Orders = #table({"OrderID", "Email"}, {{"A1", "ann@shop.com"}, {"A2", "Ben@Shop.com"}, {"A3", "CARA@SHOP.COM"}}),
Customers = #table({"Email", "Tier"}, {{"ann@shop.com", "Gold"}, {"ben@shop.com", "Silver"}, {"cara@shop.com", "Gold"}}),
Merged = Table.NestedJoin(Orders, {"Email"}, Customers, {"Email"}, "Cust", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Cust", {"Tier"})
in
ExpandedResult
| OrderID | Tier | |
|---|---|---|
| A1 | ann@shop.com | Gold |
| A2 | Ben@Shop.com | null |
| A3 | CARA@SHOP.COM | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Only A1 gets a tier. A2 and A3 come back with null, even though every customer exists. With a left outer join you at least keep the rows; with an inner join they disappear entirely, which is one of the causes covered in finding where rows go missing.
Fix: join on a normalised key
Add a key column on each side that lower-cases and trims the value, and merge on that. The original column stays untouched for display.
let
Orders = #table({"OrderID", "Email"}, {{"A1", "ann@shop.com"}, {"A2", "Ben@Shop.com"}, {"A3", "CARA@SHOP.COM"}}),
Customers = #table({"Email", "Tier"}, {{"ann@shop.com", "Gold"}, {"ben@shop.com", "Silver"}, {"cara@shop.com", "Gold"}}),
OrdersKey = Table.AddColumn(Orders, "EmailKey", each Text.Lower(Text.Trim([Email])), type text),
CustomersKey = Table.AddColumn(Customers, "EmailKey", each Text.Lower(Text.Trim([Email])), type text),
Merged = Table.NestedJoin(OrdersKey, {"EmailKey"}, CustomersKey, {"EmailKey"}, "Cust", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Cust", {"Tier"})
in
ExpandedResult
| OrderID | EmailKey | Tier | |
|---|---|---|---|
| A1 | ann@shop.com | ann@shop.com | Gold |
| A2 | Ben@Shop.com | ben@shop.com | Silver |
| A3 | CARA@SHOP.COM | cara@shop.com | Gold |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
All three orders now get their tier: Gold, Silver, Gold. In the editor, the same key is a duplicated column on each query with Transform > Format > lowercase and Trim applied, then Merge Queries on the new columns. Trimming in the same step matters, because a trailing space breaks equality just as a capital letter does. Phone numbers and postcodes need a fuller standard form, covered in standardising phone numbers and postcodes. The exercise clean both keys before merging practises exactly this, and the lesson Check key quality before you rely on it shows how to test a key before you join on it.
Why not a comparer on the merge?
Table.NestedJoin has an optional keyEqualityComparers argument, but Microsoft's reference says the feature is currently intended for internal use only (Table.NestedJoin, Microsoft Learn). A key column is documented behaviour, visible in the editor and easy for the next person to follow, so it's the safer choice.
Fuzzy merge is the other option people reach for, and fuzzy matching for inconsistent names covers when it is the right one. Its options include Ignore case, which allows matching records no matter what the case of the text (Fuzzy merge, Microsoft Learn):
let
Orders = #table({"OrderID", "Email"}, {{"A1", "ann@shop.com"}, {"A2", "Ben@Shop.com"}}),
Customers = #table({"Email", "Tier"}, {{"ann@shop.com", "Gold"}, {"ben@shop.com", "Silver"}}),
Merged = Table.FuzzyNestedJoin(Orders, {"Email"}, Customers, {"Email"}, "Cust", JoinKind.LeftOuter, [IgnoreCase = true, Threshold = 1])
in
MergedThe simulator on this site can't run fuzzy matching, so no output is shown for that block. Fuzzy matching is a similarity comparison, though, and the Table.FuzzyNestedJoin reference notes that even a threshold of 1.00 might ignore differences such as word order and punctuation. For identifiers like emails, account codes and product codes, you want exact matching on a cleaned key, not similarity.
Filters that skip rows
A filter written as [Note] = "billing" or Text.Contains([Note], "billing") misses "Billing" and "BILLING". Text.Contains takes an optional comparer, and Comparer.OrdinalIgnoreCase performs a case-insensitive ordinal comparison (Text.Contains, Microsoft Learn):
let
Source = #table({"Ticket", "Note"}, {{"T1", "Billing query"}, {"T2", "re: BILLING"}, {"T3", "Login issue"}}),
Flag = Table.AddColumn(Source, "Exact", each Text.Contains([Note], "billing"), type logical),
Flag2 = Table.AddColumn(Flag, "AnyCase", each Text.Contains([Note], "billing", Comparer.OrdinalIgnoreCase), type logical)
in
Flag2Result
| Ticket | Note | Exact | AnyCase |
|---|---|---|---|
| T1 | Billing query | false | true |
| T2 | re: BILLING | false | true |
| T3 | Login issue | false | false |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Exact is false on every row. AnyCase is true for T1 and T2 and false for T3. The exercise flag tickets that mention billing, in any case is the same task with more awkward data.
For a filter against a list of allowed values, List.Contains takes the same kind of comparer as its third argument:
let
Source = #table({"Status"}, {{"Open"}, {"OPEN"}, {"Closed"}}),
Kept = Table.SelectRows(Source, each List.Contains({"open", "pending"}, [Status], Comparer.OrdinalIgnoreCase))
in
KeptResult
| Status |
|---|
| Open |
| OPEN |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Both "Open" and "OPEN" are kept, and "Closed" is dropped. For a single value, Comparer.Equals(Comparer.OrdinalIgnoreCase, [Status], "open") does the same job as an = test that ignores case:
let
Source = #table({"Status"}, {{"Open"}, {"OPEN"}, {"Closed"}}),
Kept = Table.SelectRows(Source, each Comparer.Equals(Comparer.OrdinalIgnoreCase, [Status], "open"))
in
KeptResult
| Status |
|---|
| Open |
| OPEN |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The same two rows are kept.
Duplicates that survive
Remove Duplicates keeps "Kite", "kite" and "KITE" as three separate products. Microsoft's suggested workaround is to apply an upper-case or lower-case transform before removing duplicates (Working with duplicate values, Microsoft Learn). In M you can also pass a comparer with the column name, so the values keep their original spelling:
let
Source = #table({"Product"}, {{"Kite"}, {"kite"}, {"KITE"}, {"Reel"}}),
Exact = Table.RowCount(Table.Distinct(Source)),
IgnoreCase = Table.RowCount(Table.Distinct(Source, {"Product", Comparer.OrdinalIgnoreCase}))
in
#table({"Method", "Rows"}, {{"Exact", Exact}, {"Ignore case", IgnoreCase}})Result
| Method | Rows |
|---|---|
| Exact | 4 |
| Ignore case | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The exact version keeps 4 rows; the case-insensitive version keeps 2. Which spelling survives isn't guaranteed; the Table.Distinct reference says there's no guarantee which duplicate is preserved. If the kept spelling matters, standardise the column first. The lesson Remove duplicates on the right key covers choosing the key, and the exercise count each email once, whatever its case or spacing combines case and spacing.
Groups that split
Group By follows the same rule, so "North" and "north" become two regions with their sales split between them:
let
Source = #table({"Region", "Sales"}, {{"North", 10}, {"north", 5}, {"South", 7}}),
Grouped = Table.Group(Source, {"Region"}, {{"Sales", each List.Sum([Sales]), type number}})
in
GroupedResult
| Region | Sales |
|---|---|
| North | 10 |
| north | 5 |
| South | 7 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result has three rows: North 10, north 5 and South 7. Table.Group takes an optional comparer as its fifth argument (Table.Group, Microsoft Learn):
let
Source = #table({"Region", "Sales"}, {{"North", 10}, {"north", 5}, {"South", 7}}),
Grouped = Table.Group(Source, {"Region"}, {{"Sales", each List.Sum([Sales]), type number}}, GroupKind.Global, Comparer.OrdinalIgnoreCase)
in
GroupedResult
| Region | Sales |
|---|---|
| North | 15 |
| South | 7 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Now there are two groups: North 15 and South 7. Microsoft's reference adds a caution worth keeping in mind: when the comparer treats differing keys as equal, a row can be placed in a group whose key differs from its own. Here, "north" is summed under "North". If the label that appears matters, clean the column before grouping.
Find the case variants before they bite
Before deciding how to handle case, find out whether you have a problem. Group on a lower-cased copy of the column and keep groups with more than one spelling:
let
Codes = #table({"Code"}, {{"ab12"}, {"AB12"}, {"cd34"}}),
Lowered = Table.AddColumn(Codes, "Lower", each Text.Lower([Code]), type text),
Groups = Table.Group(Lowered, {"Lower"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Variants", each Text.Combine([Code], ", "), type text}}),
Clashes = Table.SelectRows(Groups, each [Count] > 1)
in
ClashesResult
| Lower | Count | Variants |
|---|---|---|
| ab12 | 2 | ab12, AB12 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
One clash comes back: ab12 with 2 rows and the variants "ab12, AB12". On real data this list tells you whether the variants are typing noise (fold them together) or genuinely different codes that only differ by case (keep them apart, and be careful in Power BI, as the next section explains).
Power BI adds a twist
The query is case-sensitive, but the Power BI model isn't. Microsoft's page on data types in Power BI says the engine that stores and queries data is case-insensitive and treats different capitalisation as the same value, while Power Query is case-sensitive (Data types in Power BI, Microsoft Learn). The same page describes the engine keeping the first spelling it meets and showing it for all the variants, so text can change capitalisation after loading.
Two practical effects follow:
- A column that Power Query sees as unique (ab12 and AB12) can contain duplicates in the model, which can stop a one-to-many relationship from being created.
- Names in a visual can show a different capitalisation from the source, depending on which spelling the engine met first.
The fix is the same as for merges: normalise the case in Power Query before loading, unless the codes really are different, in which case they need a different key.
Which fix to use
| Step | Case-insensitive option | Notes |
|---|---|---|
| Merge | Normalised key column on both sides | The comparer argument is documented as internal use only |
| Filter on text | Text.Contains(..., Comparer.OrdinalIgnoreCase) | Also Text.StartsWith and Text.EndsWith |
| Filter on a list of values | List.Contains(list, value, Comparer.OrdinalIgnoreCase) | |
| Remove Duplicates | Table.Distinct(t, {"Col", Comparer.OrdinalIgnoreCase}) | Kept spelling isn't guaranteed |
| Group By | Table.Group(..., GroupKind.Global, Comparer.OrdinalIgnoreCase) | Label comes from one row in each group |
| Anything loaded to Power BI | Normalise in the query | The model ignores case anyway |
Frequently asked questions
Is there a setting to make Power Query case-insensitive everywhere?
No. Case sensitivity is part of how M compares text. You choose case-insensitive behaviour step by step, with a comparer or a normalised column.
Should I use Text.Lower or Text.Upper for keys?
Either works as long as both sides use the same one. Lower case is common for emails and web addresses. The post on cleaning a messy CRM export uses it for contact emails. Keep the key in a separate column so the original spelling is still there for display.
Are column names case-sensitive too?
Yes. A step that refers to "amount" fails on a column called "Amount":
let
Source = #table({"OrderID", "Amount"}, {{"A1", 120}}),
Picked = Table.SelectColumns(Source, {"amount"})
in
PickedResult: an error
Expression.Error: The column 'amount' of the table wasn't found.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The step raises the error explained on the column of the table wasn't found page. Unlike a missed match, this one is loud, which makes it the easier of the two to fix.
The Power Query Exercises set on duplicates and keys includes several case and spacing traps, and every answer is checked in the browser.
Sources
- Working with duplicate values, Microsoft Learn (learn.microsoft.com)
- Table.NestedJoin, Microsoft Learn (learn.microsoft.com)
- Fuzzy merge, Microsoft Learn (learn.microsoft.com)
- Table.FuzzyNestedJoin reference (learn.microsoft.com)
- Text.Contains, Microsoft Learn (learn.microsoft.com)
- Table.Distinct reference (learn.microsoft.com)
- Table.Group, Microsoft Learn (learn.microsoft.com)
- Data types in Power BI, Microsoft Learn (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Power Query row count is wrong? How to find where rows go missingFind the Power Query step that loses rows: count rows after every step, list the dropped rows with an anti join, and fix the five usual causes.
- CSV quirks in Power Query: line breaks, quotes and delimiters inside valuesFix CSV imports that split rows or shift columns: quoted line breaks, commas inside values, doubled quotes, semicolon files and the fixed column count.
- Why dates turn into numbers after Power Query loads to ExcelDates that load to Excel as 46296 are Excel serial numbers. Find the untyped column in Power Query, set a Date type, and stop the table losing its format.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.