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

[mjfulke]

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

[mjfulke · Accepted solution]

 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))"

[Massimo]

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.

[KHorseman]

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.

[Daniele]

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]

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

[fritz]

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

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

[Daniele]

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.

[Massimo]

Great !    🙂     perfect.