Forum Discussion
Helper ICasing on text fields doesn't always match the data
I'm running into a strange issue. After adding a new column to an existing table within my report through the Power Query Editor's Source window (I tweaked the "SQL statement" box to add the new column), I'm finding that some of the values when running the report are incorrectly showing up in all upper caps, some incorrectly show up in all lower caps.
I've confirmed both when running the actual SQL query in SQL Server Management Studio and when previewing the data in the Power Query Editor that these records appear to be matching, but the problem only seems to occur when adding a table visualization to the report and displaying the data for this column there. I'm at a loss as to why the correct casing is getting changed by this visualization. Any guidance would be greatly appreciated.
Thanks!
6 Replies
- vanessafvg
Community Champion
maybe you should just force an upper or lower function on them in power query.
can you share your power query code? what visual are you using?
pbi_106Thanks for the response Venessa,
Unfortunately, using all upper or lower isn't going to be an option. It's a customer-facing report, and the specific capitalizations are done the way they are for a reason.
I'm using the "Table" visual.
As far as the power query code, are you referring to the code from the SQL statement? I can't share it for confidentiality reasons, but it's quite dirt simple. The columns are being pulled directly from the tables, nothing fancy, no case statements or functions being applied. Like I said, both in the power query editor and in the SQL Server Management Studio environment, I can see that the text casing is correct. Only on the "table" visual is it coming through with some inaccurate casing.
- vanessafvg
i assumed they were all a specific case. I mean the power query code from the advanced editor, you can block confidential information.
sounds like a very odd problem, its very hard to figure out the issue if you can't share more details.
Is the problem the same in other visuals such as the matrix, or even a card or other text visuals?
- pbi_106
Hey Venessa,
I'm able to reproduce the issue using a matrix or card visualization as well. I added these visualizations and added a couple of columns, including the problem column, but left everything else on these visualizations at their defaults. Still very confused as to what is happening with the casing on these values between seeing them properly formatted in the Power Query editor and seeing the casing inconsistently formatted through visualizations on the report.
- pbi_106
A few more details about the report that I'm hoping could help to give me another lead in troubleshooting this weird issue:
1. The column that I added is a free-text field from one of our apps. It allows users to enter comments about the location of where a particular customer can be found within a building.
2. There are plenty of instances in which the same value, such as "2nd floor" or "Media Center" can be populated for multiple different customers. These might have different casing depending on how the user entered it (for example, "media center", "Media Center", "media Center" or "MEDIA CENTER").
3. In my Power BI report, I can filter onto a specific customer that has "Media Center" as the value coming from the database as well as the Power BI Query Editor, but the visualization shows "MEDIA CENTER" instead.
4. Basically, the instances in which this issue is occurring are those cases in which the same value has also been entered for another customer, but with different casing. It seems like Power BI, rather than using the precise casing of the value associated with the specific customer, is often using the casing of a different customer's otherwise identical location comment value.
Hopefully this makes sense. Any way to force Power BI to use the exact casing for that customer's location value?
Syndicate_AdminAdministrator
"The problem is that DAX is not case sensitive, so if you have multiple text strings in a column that only differ on a case-by-case basis, the engine will choose one of them as a representative of all of them."
