Forum Discussion

mjfulke's avatar
mjfulke
Microsoft Employee
10 years ago
Solved

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, an...
  • mjfulke's avatar
    mjfulke
    10 years ago

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

     

     

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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's avatar
Daniele
Frequent Visitor
9 years ago

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.