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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors