Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
mjfulke
Microsoft Employee
Microsoft Employee

Generating unique keys from case-sensitive data in Power BI Desktop

Sharing with community:

 

I ran into an issue yesterday while working with a customer evaluating Power BI. They have a MySQL source database. Their dimension table uses letters for primary keys, and the case matters. An example would be:

 

A           Store #1             Oregon

a            Store #2             Washington

B           Store #3             California

 

In the Query designer for Power BI Desktop, everything  looks fine. Capitalization is preserved. Once the data is imported into PBI Desktop, everything is capitalized and then there are duplicate key values such that:

 

A           STORE #1           OREGON

A           STORE #2           WASHINGTON

B           STORE #3           CALIFORNIA

 

 

1 ACCEPTED SOLUTION
mjfulke
Microsoft Employee
Microsoft Employee

 Answer to my own question:

 

Power BI is case in-sensitive.  Solution is to add an index column OR  convert the key column data to a binary representation of value "Binary.ToText(Text.ToBinary([key column]),1))"

 

 

View solution in original post

8 REPLIES 8
mjfulke
Microsoft Employee
Microsoft Employee

 Answer to my own question:

 

Power BI is case in-sensitive.  Solution is to add an index column OR  convert the key column data to a binary representation of value "Binary.ToText(Text.ToBinary([key column]),1))"

 

 

HI, I'am a basic user of power BI. I would like to understand how to use the formula that you have indicated :"Binary.ToText(Text.ToBinary([key column]),1))" . If I create a column with the formula that you entered, Power IB telling me it does not recognize Binary.ToText. Can you help me to understand better how to do to implement your solution ? thank you so much.

Binary.ToText is a power query function. It must be done as a step in the query editor. I think you're trying to do it in the table editor.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Guys. Really struggling with this power query and the differences between the editor and the "Add column". Triyng to leran but the learning curve is steep.
Would you be so kind to help me with a practical example?
Let's say i have this context:
let
Source = Csv.Document(File.Contents("C:\Users\Daniele Conserva\Documents\CSV\POIPrimaryCategory.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"cleansedDetails.primaryCategory.categoryCode", "categoryCode"}})
in
#"Renamed Columns"

 

And i want to convert the _id column to a unique index with this formula.
Tried to add this line, but nothing...

#"Binary ID Generation" = Binary.ToText(Text.ToBinary([_id],1))  

 

Thank you very much.

mjfulke
Microsoft Employee
Microsoft Employee

Try "Binary.ToText(Text.ToBinary([_id]),1))"

I have the same Issue, no blanks, no nulls, all values unique.

It also seems connected to the size of the table (~80000 rows).

 

Hi Guys. Really struggling with this power query and the differences between the editor and the "Add column". Triyng to leran but the learning curve is steep.
Would you be so kind to help me with a practical example?
Let's say i have this context:
let
Source = Csv.Document(File.Contents("C:\Users\Daniele Conserva\Documents\CSV\POIPrimaryCategory.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"cleansedDetails.primaryCategory.categoryCode", "categoryCode"}})
in
#"Renamed Columns"

 

And i want to convert the _id column to a unique index with this formula.
Tried to add this line, but nothing...

#"Binary ID Generation" = Binary.ToText(Text.ToBinary([_id],1))  

 

Thank you very much.

Great !    🙂     perfect.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.