User Review
( votes)Working with Microsoft Power BI Key Columns
Matching your keys is an essential piece of data modeling and data analysis. Anyone working in Microsoft Power Business Intelligence runs into issues with keys. This is 100% true:
If your primary and foreign keys don’t match—your data model won’t work.
To make it easier for you to match keys, here is a helpful general outline of what to do inside M/Power Query. The steps in this outline are best when made into a habit. 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 outline is called the Clutter (CLUTR) Method. The name is pulled from the M functions. When applying M functions, you can prevent or fix many of key issues you may face as you work.
The functions in the Clutter (CLUTR) Method are:
CLEAN (Clean Text)
CLEAN 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 are little errors so they can be impossible to find 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)
Matching
LOWER and UPPER makes all text either lowercase or uppercase. The purpose of using this function is two-fold: (1) it unifies the text on both sides of a key-pairing, and (2) it makes 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.
Duplicates
Keeping all uppercase or all lowercase naturally helps with removal of duplicates. Duplicates arise without standardizing text through sources. If there is text with one case that is different from an otherwise similar text—both versions will be kept in M. To make sure these duplicates are removed, run a duplicated function on that column.
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)
TRIM 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)
REMOVE DUPLICATES removes all the duplicates within your selected columns (or column, in the case of Distinct List). This is essential for one-to-many relationships to be successfully formed. This should be used on every table expected to be on one side of a relationship.
Ready
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.
Learn More
JourneyTEAM
Article by: Dave Bollard – Head of Marketing | 801-436-6636
JourneyTEAM is an award-winning consulting firm with proven technology and measurable results. They take Microsoft products; Dynamics 365, SharePoint intranet, Office 365, Azure, CRM, GP, NAV, SL, AX, and modify them to work for you. The team has expert level, Microsoft Gold certified consultants that dive deep into the dynamics of your organization and solve complex issues. They have solutions for sales, marketing, productivity, collaboration, analytics, accounting, security and more. www.journeyteam.com
