PowerBI dealing with text case differently in M query compared to the data model

In 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.