Case sensitivity is one of the more confusing aspects of Power BI: while the Power Query engine is case sensitive, the main Power BI engine (that means datasets, relationships, DAX etc) is case insensitive. Imke Feldmann shows how to make Power Query case insensitive through custom M here; in this blog post I’m going to concentrate on what case insensitivity means in datasets and DAX and show a way to (kind of) work around it.
Starting with a simple example, let’s say you have a data source that contains data that looks like this:
This is what the data looks like in the Power Query Editor which is, as I said, case sensitive – which means that it sees “A” and “a” as different characters, so you see six distinct characters in each row of the table. This could be data from any data source; to make things easy I’ve used the following M query to generate this table:
let
Source =
#table(
type table
[MyTextColumn = text],
{
{"A"},
{"a"},
{"B"},
{"b"},
{"C"},
{"c"}
})
in
SourceWhen this data gets loaded into a Power BI dataset, however, you’ll see the following in the Data pane of the main Power BI window:
Because the main Power BI engine is case insensitive – so for example “a” and “A” are considered as the same character – when this data is loaded in, it only sees three distinct characters and you can’t be sure whether you’ll get the lower case or upper case character stored twice. This is just the way it works; while an instance of Analysis Services Tabular, which is basically the same engine that is found in Power BI, can be either case sensitive or case insensitive, this option isn’t available in Power BI. There is an idea to support case sensitivity as an option in Power BI here and while there are some valid scenarios where you need this, in my experience case sensitivity causes a lot of problems so I can see why it wasn’t a priority.
What’s more you’ll find that DAX calculations are case insensitive too. If you try the following DAX expression to create a calculated table:
Demo Table =
DATATABLE(
"MyTextColumn",
STRING,
{
{"A"},
{"a"}
}
)You’ll get this:
DAX measures behave in a similar way. Given the following table:
If you use the following measure in a table visual:
Demo Measure =
IF(
SELECTEDVALUE('Demo Table 2'[MyIntColumn])=1,
"A",
"a"
)…you’ll see:
The only way you can work around this case insensitivity is to make text values that would otherwise look the same to the Power BI engine somehow different. One way of doing this would be to add some extra characters to your text. You might think adding some extra spaces would be the way to go; revisiting the first M query shown above, you could add a space to every lower case character in the table like so:
let
Source =
#table(
type table
[MyTextColumn = text],
{
{"A"},
{"a "},
{"B"},
{"b "},
{"c "},
{"C"}
})
in
SourceBut this doesn’t work because another little-known behaviour of the Power BI engine is that all leading and trailing spaces are trimmed when text is loaded. Anyway, spaces may not be visible but they still take up… well space. A better option – and one that actually works – is to use the Unicode Zero-Width Space character and the M Character.FromNumber function instead like so:
let
Source =
#table(
type table
[MyTextColumn = text],
{
{"A"},
{"a" & Character.FromNumber(8203)},
{"B"},
{"b" & Character.FromNumber(8203)},
{"c" & Character.FromNumber(8203)},
{"C"}
})
in
SourceThe great thing about this character is that although it’s there, it’s invisible and takes no space. When you load this last M query into Power BI you see the following table:
Let’s finish off with a more advanced example of how to use this rather excellent character. Say you have the following source data returned by an M query called SourceData:
Of course, when this table is loaded into Power BI, you see the following:
BUT, the following M query takes this table and for each row goes through the text in the OriginalText column and adds a zero-width space after each lower case character:
[sourcecode language='html' ]
let
Source =
SourceData,
ToList =
Table.AddColumn(
Source,
"Chars",
each Text.ToList([OriginalText])
),
LowerCaseChars =
{"a".."z"},
AddInvisibleChars =
Table.AddColumn(
ToList,
"AddInvisibleChars",
each
List.Transform(
[Chars],
each
if
List.Contains(LowerCaseChars, _)
then
_ & Character.FromNumber(8203)
else _
)
),
RecombineList =
Table.AddColumn(
AddInvisibleChars,
"OutputText",
each
Text.Combine([AddInvisibleChars]),
type text
),
RemovedOtherColumns =
Table.SelectColumns(
RecombineList,
{"OutputText"}
)
in
RemovedOtherColumns
[/sourcecode]
When you load the output of this query into Power BI, because the zero-width spaces after each lower case character make Power BI see each piece of text as being different, it looks like case has been preserved even when it hasn’t:
You can download the Power BI file for this last example here.
[Thanks to Greg Galloway, Akshai Mirchandani and Jeffrey Wang for their help with this post]
Hi Team,
Definitely worth to read it.
Andrii
Great post — I didn’t know that “A” and “a” become the same value when loaded!
I noticed that sometimes Power Query is case-insensitive: when query folding takes place and the underlying data source is case-insensitive.
DAX is sometimes case-sensitive — most of the time it’s clear when this is so: FIND and SEARCH perform the same function and are case-sensitive and case-insensitive, respectively.
Hi Chris,
I have just the opposite problem…
I’ve created the different “Level” columns (15) in an SSAS Tabular model, from a Parent Child hierarchy. I need to sort TOTAL ASSETS and Total Assets as different/unique values in Level1Name. The GL Account Number is different for each, thus the error. This has caused quite a headache as I keep getting the duplicate value error in my Tabular model. We’re trying not to append a ‘1’ to values as this is what the client will see.
I reviewed the solution above, but this won’t work with a table as large as ours. We don’t have the option to apply sorting in Power BI.
Thoughts?
BW
Hi Chris
If I create a column with many leading spaces and load them to Desktop, the Data view seems to show them all, but the table visual seems to show none, but if I do something like this
appendcol = “aa” & ‘text with both spaces'[Message] & “zz”
and put them into a table visual, I can see a consistent gap between the “aa” and the text for data that had leading spaces, but not for the others.
Here is my data.
Message
This message does have spaces 1
This message doesn’t have any 2
This message does have spaces 3
This message doesn’t have any 4
This message does have spaces 5
This message doesn’t have any 6
Also, with this data, when you sort ascending the appendcol, it puts the them in this order (5,3,1,2,4,6) and when I export the data as a CSV from the visual this is the output.
Message,appendcol
This message does have spaces 5,aa This message does have spaces 5zz
This message does have spaces 3,aa This message does have spaces 3zz
This message does have spaces 1,aa This message does have spaces 1zz
This message doesn’t have any 2,aaThis message doesn’t have any 2zz
This message doesn’t have any 4,aaThis message doesn’t have any 4zz
This message doesn’t have any 6,aaThis message doesn’t have any 6zz
As far as I can see, leading spaces don’t get completely trimmed.
Am I doing something wrong?
Many thanks
Oliver
Hi Chris
If I create a column with many leading spaces and load them to Desktop, the Data view seems to show them all, but the table visual seems to show none, but if I do something like this
appendcol = “aa” & ‘text with both spaces'[Message] & “zz”
and put them into a table visual, I can see a consistent gap between the “aa” and the text for data that had leading spaces, but not for the others.
Here is my data.
ID,Message
1, This message does have spaces 1
2,This message doesn’t have any 2
3, This message does have spaces 3
4,This message doesn’t have any 4
5, This message does have spaces 5
6,This message doesn’t have any 6
Also, with this data, when you sort ascending the appendcol, it puts the them in this order (5,3,1,2,4,6) and when I export the data as a CSV from the visual this is the output.
ID,Message,appendcol
5, This message does have spaces 5,aa This message does have spaces 5zz
3, This message does have spaces 3,aa This message does have spaces 3zz
1, This message does have spaces 1,aa This message does have spaces 1zz
2,This message doesn’t have any 2,aaThis message doesn’t have any 2zz
4,This message doesn’t have any 4,aaThis message doesn’t have any 4zz
6,This message doesn’t have any 6,aaThis message doesn’t have any 6zz
As far as I can see, leading spaces don’t get completely trimmed.
Am I doing something wrong?
Many thanks
Oliver
Hi Chris
If I create a column with many leading spaces and load them to Desktop, the Data view seems to show them all, but the table visual seems to show none.
Here is my data.
ID,type,c1,c2,c3,c4,c5
1,has space, a , b , c , d , e
2,no space,f,g,h,i,j
3,has space, k , l , m , n , o
4,no space,p,q,r,s,t
5,has space, u , v , w , x , y
If I do something like this
c12 = “*” & ‘text with spaces 2′[c1] & “*”
c22 = “*” & ‘text with spaces 2′[c2] & “*”
c32 = “*” & ‘text with spaces 2′[c3] & “*”
c42 = “*” & ‘text with spaces 2′[c4] & “*”
c52 = “*” & ‘text with spaces 2′[c5] & “*”
and put them into a table visual, I can see a consistent gap between the first “*” and the text for data that had leading spaces, but not for the others.
When I export the data as a CSV from the visual this is the output.
ID,type,c1,c2,c3,c4,c5,c12,c22,c32,c42,c52
1,has space, a, b, c, d, e,* a*,* b*,* c*,* d*,* e*
2,no space,f,g,h,i,j,*f*,*g*,*h*,*i*,*j*
3,has space, k, l, m, n, o,* k*,* l*,* m*,* n*,* o*
4,no space,p,q,r,s,t,*p*,*q*,*r*,*s*,*t*
5,has space, u, v, w, x, y,* u*,* v*,* w*,* x*,* y*
As far as I can see, trailing spaces do get trimmed, but leading spaces don’t get completely trimmed.
Am I doing something wrong?
Many thanks
Oliver
I don’t know, sorry.
Hello, Chris.
When you put an “&” in your code instead of plain “&” it looks a little confusing. When I see a semicolon inside M code – I start looking for “section” immediately =)
Best regards
Hi Chris, first of all, thanks for the nice blog. 1 thing I didn’t understand; when I enter a string “This Is test” and load the data in Power BI, it is showing “This is Test”. So Is->is and test->Test. Same behavior if I put ‘This IS test’ -> ‘This is Test’. Not sure why this is happening? is it a bug?
Sounds strange. Do you have a table with multiple rows in it?
Thank you so much for this! I spent hours not believing what my eyes were seeing. Your article really helped me out. Thank you.
Hi Chris,
I am trying to use your code here to deal with a Salesforce account id value, which is alphanumeric and case sensitive. I see how it works when dealing with a one column example. However, I’m trying to apply it to data that’s getting pulled in from a multi column spreadsheet. I’m getting an error (“WE cannot convert the value “name of my query” to Type Table) when I set my source to point to the query that is pulling in said spreadsheet. Any suggestions on how to tweak your code to account for this?
Thanks!
It sounds like you are returning something that isn’t a table from a query somewhere, but it’s impossible to say more.
Hi Chris,
I am working with Sales Force URL data and the fact that Power BI is Case insensitive creates incorrect URL information. I used your code and it worked great. However The URL doesn’t seem to work because there are hidden spaces. The original URL length is 44 this URL with code varies because of the zero width space though is hovers around upper 60s to low 70s.. Do you have any suggestions. As you mention the spaces are in between letter so not sure what to do. Maybe there is some sort of replace. However if I use the replace that might cause the letters to revert to case insensitive. Furthermore I am not sure what to look for to replace. You also mention that “it looks like the case has been preserved even when it hasn’t”. Does that mean that that it is actually different then it looks.
Thanks,
Rob Wolf
What are you trying to do with your URLs? If you use this technique on a URL then you won’t be able to use that URL afterwards, as you’ve found.
I use them in a report. The user can click on the value ( I use URL conditional formatting ) and it takes them to the original record in Salesforce. However since the case was changing it took the user to the wrong record. In the end the it was Solved by the Alice the person who had the problem in the Power BI Community forum (there was a link in one of the attempted solutions to this blog post). She concatenated the Internal ID to the Salesforce location (“https://YOURCOMPANY.my.salesforce.com/) and somehow it worked. I say somehow as the Internal ID also has lower case and upper case letters.
So it is still a mystery, but I can’t argue with success so I will use the solution.
Thanks for your time,
Rob
Hi Rob – do you by chance have that URL from the Power BI Community forum that led you here? I’m having some very weird behavior in a report where i’m trying to provide the SFDC URL. I was finally able to get Chris’s code to work so I could join data from two tables with SFDC account IDs, but I’m having a hell of a time trying to get the URL to work correctly. I get the zero width space issue if I try to concatenate the base URL to the revised SFDC ID. I split the 18 character SFDC ID into separate columns of 15 and 3 characters. If I try and concatenate the base URL to the column with the 15 character ID value, in some (not all) cases, the very 15th character is changing from a lower case value to an upper case value. It was just a fluke that I even found this – when I was doing a demo and name of the vendor in SFDC was not what I was expecting. Anyway, I wanted to see if that Power BI Community post might be able to help me figure out how to overcome the hidden space issue. Thanks!
Hi Rob – don’t worry about digging up that link. I’ve been doing some more research and I don’t think it will help with my issue.
Hi Chris,
Your solution here is a pretty amazing use of the tools in the M language toolbelt. However, I have hit a roadblock that many others I suspect have hit before me. I have a large number of Salesforce records that I am bringing into a PBI report. Adding the Zero-Width Space character works great in being able to create the 15 character SFDC Account ID value that is actually unique, and then use it to be able to create relationships between different tables with SFDC data.
Unfortunately, I need to take this one step further. I need to be able to construct the URL for the account record in SFDC in my Power BI report. If I concatenate the base URL and the updated Account ID value with your solution, the browser sees and attempts to use the Zero-Width Space character(s) in the URL being sent to it – resulting in a bad link that doesn’t work.
If I concatenate the non-manipulated value of the 15 character SFDC Account ID value, this will work only if no other SFDC Account ID values with the same alphanumeric values (but different cases for the alpha characters) are a part of the data set because Power BI stores the same value with the same case settings as the first one. In other words, if I have these two (unique) SFDC Account ID values: 0023F00161TrHhP & 0023F00161TrHhp, Power BI stores the first value with the last character upper case P in the row that has the value with the lower case p – both rows have an Account ID value of 0023F00161TrHhP.
I guess this is by design (to compact data and save space?), and I have found no way to work around this scenario unless the browser could be configured to ignore the Zero-Width Space character that it sees from the updated values we create using your solution – and even that seems far fetched.
So, I leave problem this with you and others out there to chew on. Maybe this will be a seed that germinates into an idea of how we can resolve this challenge in (the hopefully very near) future.
Thanks for all the effort you put into your blog – much appreciated!
Trey
Chris,
Really helpful info as usual.
I ran into an interesting edge case today that I hadn’t considered before concerning foreign-language characters, specifically the Germanic umlaut.
Power BI seems not to recognize “Ö” and “ö” as different cases of the same letter, so it was actually causing relationships to fail when there was a case mismatch between tables.
Interestingly, performing a Text.Upper conversion on the key field in Power Query and re-loading the data immediately fixed the relationship, so evidently Power Query does recognize the two as different cases of the same letter.
Probably a good example of why we shouldn’t rely on case-insensitivity especially if foreign characters may be involved – from now on we will force case in our key fields upstream prior to any relating/joining.
Anyone solved this “problem” for the use of URLs?
In the report im able to see the right link with this apporach but i can’t mark it as Web-URL because PowerBI isnt able to notice the link anymore and i cant copy the link to the browser because the browser notices that there are zero-width spaces, which means that the link is obviously not the same link anymore.
Thanks!
Thanks Chris!
And as I have found today, case insensitivity occurs in row level security too, i.e. these are all equivalent and all work the same
[Email] = “another.person@company.co.uk”
UPPER([Email]) = “another.person@company.co.uk”
LOWER([Email]) = “another.person@company.co.uk”
more precisely I should have said same applies if I use “Another.Person@company.co.uk” too!
This helped me a lot, thanks!
This was so helpful. Thanks!
Unfortunately, if values are being copied from the report (table visual) and pasted elsewhere (like a username field), the consumer may reject because ‘aB’ ‘a&8903;B’
However, and fortunately, once the dataset has imported the values, you can convert them back to a simple string with NewMsr := SUBSTITUTE( SELECTEDVALUE( Table[MyOutputText] ), UNICHAR( 8203 ), “” ). This will copy and paste like the input source (unless you are stripping an intentional invisible space)
This was very useful and I was able to create a case sensitive column of, you guessed it, Salesforce ids.
I was wondering if you had any suggestions regarding how I could make this new column searchable? My use case is that the user needs to be able to search for records using the case sensitive column. As far as I can see, when testing, the text filter search box does not find these new values.
Thanks in advance!
Hey, my problem is that i want my dashboard to contain URLs. Some of the URLs just differ regarding the upper and lower cases. This means that PowerBI sees them as the same link, wich again means that im not able to land on the right website for one of those links. Is there any way so solve this problem?
Thanks, already!
Oh my – Thank you so much for this!
I have only added the space when it is lowercase and it has worked perfectly:
text.Upper([column]) [column] then [column] & Character.FromNumber(8203) else [column]
That alone works for me. Thanks Chris for the article.
I got this working for Salesforce URLs that only differ by case. In Power Query I updated the URLs by appending unique values from another column in my data as an extra parameter to the URL query string, which makes the URLs different for the Power BI engine. As Salesforce isn’t expecting and doesn’t recognise the extra parameter, it just gets ignored and the link still works fine.
The Power Query code looks like this:
MakeURLsUnique = Table.ReplaceValue(#”Filtered Rows”, each [Link], each [Link] & “&makelinkunique=” & [OtherColumnWithUniqueValues], Replacer.ReplaceValue, {“Link”})
Who at Microsoft could take such a stupid decision? How is this acceptable? I have just started playing with Power BI and I am already disappointed. My primary key is text and OF COURSE CASE MATTERS and I have no idea how to overcome this in relationships with other tables.