If your primary and foreign keys don’t match—your data model won’t work.
Anyone working in Microsoft Power Business Intelligence runs into issues with keys. This is 100% true: Matching your keys is an essential piece of data modeling and data analysis.
Making your keys match is an essential piece of data modeling and data analysis. We have all run into a few issues with keys while working with Power BI. To help you out, here is a general outline of what to do with your keys inside M/Power Query. Use them prior to creating relationships in Power BI desktop, and apply them after creating the relationships when you realize they need to be improved.
This is the Clutter (CLUTR) Method. This name pulls from the M functions. When you apply M functions, you can prevent or fix many of these key issues.
The CLUTR Method functions are:
CLEAN (Clean Text) removes line feeds (#(lf)) and other non-printable characters from text values. Regardless of the many sources, these non-printable characters and error-causing text show up often. If one side of a key pairing contained “#(lf)” and another did not, there is no match. These can be impossible-to-find little errors if you aren’t doing a thorough search. CLEAN should be used every time, or at least as soon as any issue arises.
LOWER and UPPER (Lower Text and Upper Text) makes all text either lowercase or uppercase. The purpose of using this function has two reasons, first unifying the text on both sides of a key-pairing, and second, making the removal of duplicates more accurate.
Sources tend to approach the casing of text differently. This is true in Power BI as well. For example, M uses casing differently than DAX. By making all keys uppercase or lowercase, you ensure the text keys are the same.
This naturally leads to the removal of duplicate issues that arise without standardizing text through sources. If a removed duplicated function is run on a column, and there is text with one case that is different although similar to another text—both versions will be kept in M.
Sometimes you do want a duplicate made, but more often than not you don’t. This is important. One-to-many or one-on-one relationships are impossible if the intended side has just 1 pair of the same value. Remember M and DAX reads cases differently, removing duplicates done in M could still leave duplicates in DAX.
TRIM (Trim Text) removes spaces before or after in the field. These spaces can be impossible to spot, much like the additions the Clean function removes. TRIM (and CLEAN) should be used every time you have a text key column, or as soon as issues arise with your text key column.
REMOVE DUPLICATES (Distinct Table or Distinct List) removes all the duplicates within your selected columns (or column in the case of Distinct List). As mentioned earlier, this is essential for one-to-(M)any relationships to be successfully formed. This should be used on every table expected to be on one side of a relationship.
Employing the CLUTR method will get you through any issues you’re having with a text key column. Each of these functions is incredibly easy to use through UI in Power BI’s Power Query (as Doug Burke so kindly pointed out). It’s best to use these functions on all text key columns as a habit. You’ll not only avoid regular errors, but you’ll also avoid the unseen errors that mismatching keys produce.
Let us know how it works for you in the comments below.