Forum Discussion
Helper IPowerBI dealing with text case differently in M query compared to the data model
Hey Everyone,
I have a dim table that I'm creating from unique values of an existing column in my data model.
I'm taking a field of brands from customers and creating a dim with the following format:
brand = column of brand names
customer = column of the customer name
customer_brand_key = a concatenation of customer and brand. The reason for this is because you can have the same brand across multiple customers, so this is an explicit key to make the join
example rows:
customer, brand, customer_brand_key
cust1, bubbles, cust1_bubbles
cust1, BuBBleS, cust1_BuBBleS
In the Power M Query, doing a 'remove duplicates' step these two are treated as 'different' and so they are both kept. This is expected behaviour for me. But, when I load the table into the data model, these two rows are treated as duplicates, so I get the error related to 'duplicates in a 1:many key'.
Why are they treated differently in these instances and what's the best approach to solving this issue? I can't lowercase everything because I must retain the case due to this being an embedded report that gets variables passed in the API for filtering on brand.
5 Replies
ppm1Solution Sage
This is a common issue. M (query editor) is case sensitive, while DAX is not. You will need to make the case match (upper, lower, capitalize) before your remove duplicates.
Pat
I have a free text field in application where user entered values as
All products
ALL PRODUCTS
all products
All Products
But while loading the data from Power Query (Data source : Postgresql) to Power bi it is normalizing and displaying as 'all products' now my business team wants this to be display as it is available in application.I checked and found that it is default behavior of Vertipaq engine but my business requires data as team is using these reports for compliance related
- ribisht17
Super User
Hi gcam032
This is a classic Power BI quirk—what you're seeing is a case sensitivity mismatch between Power Query (M) and the Data Model (DAX).
Power Query (M) is case-sensitive by default. So cust1_bubbles and cust1_BuBBleS are treated as distinct values.
Power BI Data Model (DAX) is case-insensitive. When you load the data, it treats those two keys as duplicates, triggering a 1: cardinality violation* if you're trying to use them as a unique key in a relationship.
Best Practice Workarounds
1. Preserve Case but Create a Case-Insensitive Key for Relationships
Create two keys:customer_brand_key_display → retains original casing for display and filtering
customer_brand_key_model → lowercase version used for relationships
// In Power Query
Add Column → customer_brand_key_model = Text.Lower([customer] & "_" & [brand])
Then:Use customer_brand_key_model for joins and relationships
Use customer_brand_key_display for visuals and filters
This way, your embedded report can still pass case-sensitive filters, but the model remains stable.Regards,
Ritesh
Community Champion
Please mark the answer if helpful so that it can help others
Dance-Sing with Data -BI & Analytics
Shubham_rai955In Power BI, Power Query (M) is case sensitive, so it treats differently cased text values (e.g., “bubbles” vs. “BuBBleS”) as distinct, which is why both are kept after removing duplicates in M. However, the Power BI data model is case insensitive and normalizes text values, treating “bubbles” and “BuBBleS” as the same. This causes duplicates and key errors when loading data into the model.
To solve this without losing case sensitivity in the API or visuals, consider creating a unique key in Power Query by appending an index or suffix. Avoid relying solely on text case for keys inside the data model, as it ignores casing for comparisons.
This difference arises because Power Query and the data model use different string comparison rules by design.
- Ahmedx
