Replacing values in a text column from related table

For the ~5-6K rows of this example, I used Sean's DAX solution and made the relationship "fuzzy" coupled with DataChants ideas of stripping consistent known issues out ahead of time (in DAX).

• Step 1 was cleaning up the incoming names to get rid of a lot of stuff.  I did this in the col create DAX.

SystemInfoByRept = 
    	SELECTCOLUMNS(
		FILTER(2014_ALPHA,
		[Text] = "SearchString"),
		"ReptRecNo",2014_ALPHA[ReptRecNo],
		"SystemName",TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(2014_ALPHA[Text],"NAME:",""),".",""),"INC",""),"LLC","")))

• You can see that every name--whether it's a "good one" or not will at least be clean and reliably findable plus things that are always wrong are already removed.

• Step 2 was building a list of corrections from the SystemName.  I put all the names recorded by providers in Excel--got a distinct list and culled out the ones that didn't need changing.  What's left are the ones that need a change...for example "System of Bahston" should be "System of Boston".  Some are misspells, others are unconforming names...but a list of several hundred corrections.  This list, in excel is easily updated and is simply a list of changes--Before and After.
• Step 3 Query this to make a table...and relate many to one with the Raw System Names.  Obviously the ImproperSystemNames need to be Unique

• And the last step was to run the Related "lookup" and if a HospitalSystemName matches a Corrected System Name row, use it otherwise use the original.  So for each row of SystemNames...it uses RELATED to see if it's improper.  ISBLANK() tests if something is found--and if it's not Blank..fetches the Corrected System Name using RELATED again.

UniformSystemName = 
	If(ISBLANK
		(RELATED(SystemNameCorrections[ImproperSystemNames])),
		SystemInfoByRept[HospitalSystemName],RELATED(SystemNameCorrections[Corrected System Name])
		)

There's a lot of trickery in the syntax here, so many thanks to Sean for being the pattern guy on this.  RELATED is used in to see if a record in the ImproperSystemNames exists AND in fetching the Corrected System Name.  And all of this in a Column create!!

Tom

Yes, moving towards "fuzzy lookup" this is the way to go. Looking forward your approaches here.

But I'd expect it costing performance. So for unconditional joins expecting that Table.Join performs much faster.

I have an entire blog article devoted to lookups in staging area of the Community Blog if you have access to it.

http://community.powerbi.com/t5/Community-Blog-Staging/Good-Ol-VLOOKUP-The-Ultimate-Guide-to-Lookups-in-Power-BI/ba-p/31095

Very similar DAX to yours, in fact, I think your ISBLANK is a little cleaner. The blog article covers both DAX and M lookups.

Greg_DecklerThis sounds interesting, but I'm being denied access...peraps I'm not the only one.  Do I need to sign-up separately?  Tom

Go to the Community Blog link and on the right hand side use the "Message Admins" button to request access.