Forum Discussion

ThomasDay's avatar
ThomasDay
Icon for Impactful Individual rankImpactful Individual
10 years ago

Replacing values in a text column from related table

Hello fellow DAX'ers.  I do find text handling complicated.  Hope one of you can help me out.

I have the following situation where entries in a system name column are very inconsistent...  For example, the Red items are variants of a proper consistent system name.  

 

I've created a list of inconsistent values and their corrections.  There will be a modest number of corrections and MANY that are just right--hence the desire to just have a list of corrections.

 

I want the following where only the names needing correction are updated.  I've tried dozens of variants and need a starting strategy if you don't mind.  The things I've tried have been of the form: and the issue is making a scalar out of the text rows in the relatedtable 

Any patterns or strategies would be greatly appreciated and thanks in advance! Tom

 

 

UniformSystemName = 
//Create a column in the Hospital Demographics Table--to provide an iterator/row to work with
//Find out if there is a correction (HowMany =0 says no, HowMany = 1 says yes)
//there will not be more than one record found.
	VAR HowMany = COUNTROWS(RELATEDTABLE('SystemNameCorrections'))
	RETURN
	IF (
		HowMany = 0, HospitalDemographicsTable[HospitalSystemName],
//If found one, use the corrected
		IF( HowMany = 1, FIRSTNONBLANK(SystemNameCorrections[ConsistentName]),
//Ignore anything else
		"")
		)

24 Replies

  • DataChant's avatar
    DataChant
    Icon for Most Valuable Professional rankMost Valuable Professional
    10 years ago

    Did you consider using the Query (aka Power Query) to cleanup and normalize the company names? By using Power Query for the data preps, you will have a cleaner and simpler design. If you are interested in this direction, I can show you how to create queries that will normalize your company names to the desired format.

    • ThomasDay's avatar
      ThomasDay
      Icon for Impactful Individual rankImpactful Individual
      10 years ago

      Thanks, I'd welcome knowing how to do that using a query.  That sounds promising.

       

      I have a table of known corrections which can be applied to each new quarter's data...and then will surely see a few more corrections to be made as folks invent new ways to make the names inconsistent.  I'll then create an updated list of corrections and want to clean up the system names again.

       

      AND I'd love to know how to do this in DAX (presumably create column) if anyone has a strategy/pattern for that.  In the DAX world, I need to know more about (the confounding) text column handling.

       

      So what would you do to normalize the system name in this example?

      Tom

      • Sean's avatar
        Sean
        Icon for Community Champion rankCommunity Champion
        10 years ago

        ThomasDay Wouldn't it be nice if we had Data Validation at every level of data entry. I'll be following this post now too...

         

        EDIT: By the way if you've ever worked with Land Records - if a Name is misspeled on a Vesting Instrument such as a Deed unless there's a correction filed of record - you have to go with the mistake - hence - Owner Numbers (which stay consistent regardless of how an owner is listed on their various properties)