GUIDs get transformed to lowercase on import

[Anonymous]

Today I ran into something I couldn't get my head around at first (also I couldn't find the solution for it on this forum). I have found a workaround which fixes my issue. This post has 2 targets:

- If my workaround actually is the best one out there, it could help others, or (more likely)
- I hope someone has a real solution to this problem.

My original issue
I am working on visualising the data from System Center Virtual Machine Manager. Since I'm quite new to PowerBI I started out with the default database connection (which does a SELECT * FROM [database]) and built my report on it. One of the steps in cleaning the data was removing 20+ unused columns.
I wanted to change my source so the 20+ unused columns never would be included in the dataset to begin with, for this I used the following T-SQL query:

SELECT 
  VHDId,
  VHDType,
  MaxSize
FROM 
  tbl_WLC_VHD

These 3 columns are the only ones I need and the query seemed to work as intended, untill I had a look at the data. My merges were broken! After I had uttered some swearwords and challenging my sanity I noticed the VHDId column which contains a GUID had been converted to lowercase.

The screenshots below show the column in PowerBi and in SQL.

Looking around this forum I found out PowerBI checks the first character and then decides if the entire column should be lowercase or uppercase. The solution for me would be to sort the column in my T-SQL query and make sure it would find letter as a first character. So my second attempt was the following import.

SELECT 
  VHDId,
  VHDType,
  MaxSize
FROM 
  tbl_WLC_VHD
ORDER BY VHDId DESC

Where I expected the first few hits to all start with an "F" I got the following result. This query did work in keeping the import uppercase, but I could never be sure it wouldn't break in case the first record ever got deleted (not very unlikely with a virtual disk).

Further research led me to the conclusion a GUID isn't interpreted as a string since it actually is an array of bytes and sorting works on a whole other level (if you want to go into that rabbit-hole, be my guest: https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=102450).

My "solution"

This isn't really a solution in my opinion. Why would PowerQuery transform the data to begin with? After this ordeal I totally agree with this post: https://blog.greglow.com/2018/01/23/opinion-case-sensitivity-pox-computing/.
But what did work for me? I converted the GUID to a string myself since PowerBI will interpret it as a string anyway. This way I could actually sort it to make sure a letter would always be my first result (unless I had a very small dataset ofcourse). To this end I adjusted my query to the following.

SELECT 
  CONVERT(nvarchar(36),VHDId) as VHDId,
  VHDType,
  MaxSize
FROM 
  tbl_WLC_VHD
ORDER BY VHDId DESC
 

This change resulted in the ordering I wanted, which also gave me a GUID in uppercase and successful merges.

If anyone knows a better way to work around this issue, please let me know. At least I got it working for now though.

Edit I have found out the ORDER BY isn't even needed. PowerQuery won't mess up the GUID if casted as a string. So without the ordering the query is even faster.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Where do you do the merges? If you do the merges in Power Query Editor, after connecting to the data, add a step to UPPERCASE the VHDId column before merging operation. 

Or enable fuzzy matching and check Ignore case when merging queries. 

Would this be helpful? 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Thanks, both are options which should prevent extra load on my database. I am still learning a lot about powerBI and totally missed these options.