Replacing values in a text column from related table

ThomasDay - Hey Thomas, I just created this in another thread:

Here is an example of a lookup function that uses a database table as a source of its information:

let fnHex2DecFromDB = (input) =>

let
    Source = Sql.Database("MySQLServer", "MyDatabase"),
    dbo_Hex2Dec = Source{[Schema="dbo",Item="Hex2Dec"]}[Data],
    Record = Table.First(Table.SelectRows(dbo_Hex2Dec, each [Hex] = input)),
    Result = Record.Field(Record,"Dec")
in
    Result
in
    fnHex2DecFromDB

In the example above, "MySQLServer" is the name of the SQL Server, "MyDatabase" is the name of the database. In that database on that server I have a table called "Hex2Dec" with two columns named "Hex" and "Dec". The rows are what you would imagine them to be for hex to decimial conversion. Basically, the premise here should work for any lookup situation that has unique lookup values. To use this, just create a new column with the formula:

=fnHex2DecFromDB([TextColumn])

Thank you very much Greg_Deckler  I'm traveling (today)/tomorrow and look forward to building it into my model when I get back.  It will be a real assset!  Several transformations have been done by a SQL coding friend so I have SQL Express on my computer--so I'm sure I can load the table there.  

Thank you again,

Tom

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

Cool ThomasDay, let me know how it goes! I am actually in the process of creating a blog article on this very topic demonstrating lookups in Power BI using DAX as well as M, etc.

So, how about using it to fix an existing column of data.  For each system name (skip blanks)--would I call the function--check if it has an entry in the conversion table and substitute.  I'm sure that's not quite it's standard use case--but a logical extension for ETL work.  Perhaps create a new column from the existing column?  Not quite sure what the wrapper to the function would be in that case.

Let me know what you think about that and thanks,

Tom

I would create a new column and do something like the following (this is psuedo-code)

= if [System] <> "" then if fnLookup([System]) = "" then [System] else fnLookup([System]) else ""

Again, that's psuedo code, you may have to break that if statement up into two columns not even sure you can do nested if's in M, will have to try that. In any case, you're getting ahead of me, I'm still working on the blog article and I have this exact scenario in mind for solving in DAX as well as M.

Greg_Deckler

thinking this "lookup"-function already exists in M: Merge (Table.Join)

ImkeF True to a point, but in my opinion, Merge does not cover all of the possible use cases because you don't have complete and utter control over the "lookup". Essentially, Merge suffers from the same limitations as you have when doing simple relationships in Power BI Desktop.

I think I don't understand what you mean here. Will you cover that in your upcoming article?

Yes, it will be in there. The basic concept is, if you do it via relationships or Table.Join, you effectively have to have all matching values, even for "good" values. Think of the instance where you have a list of known data errors that you wish to clean up and replace. Are there methods for handling that? Sure, it can be done but it gets messy with relationships and Table.Join(s). One method is to have translations for "good" values to that same "good" value. Or, there is the scenario where there are exceptions, so you want to replace this with that but not in this particular circumstance. Again, are there more than one way to solve that? Sure, but again it can again get very messy. The article I am writing deals with all of those different scenarios and presents about half a dozen or more ways of solving lookups along with all of the pros and cons.

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.