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...
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)

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

    In the exmaple below, I duplicate the original column to the column "Normalized" and perform the fixup of the new column by lowercasing the text, removing the ending dot and finding text that ends with "inc" to replace it with " inc".

     

    You can copy the entire M statement to see the exmaple.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCq4sLknNVfDMS9ZTitWB8YFcJJ4CjOuRX1zgXpRfWoAQ8Q8OcA/yDw3w9HPGVAI0MhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Column1]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Column1", "Original"}, {"Custom", "Normalized"}}),
    #"Lowercased Text" = Table.TransformColumns(#"Renamed Columns",{{"Normalized", Text.Lower}}),
    
    fnReplaceSuffix = (inputText, suffixToRemove, suffixToAdd) =>
        if Text.EndsWith(inputText,suffixToRemove) then
                Text.ReplaceRange(inputText,Text.Length(inputText)-Text.Length(suffixToRemove),Text.Length(suffixToRemove),suffixToAdd)
        else inputText,

    RemoveDots = Table.TransformColumns(#"Lowercased Text",{{"Normalized", each fnReplaceSuffix(_,".","")}}),
    
    HandleInc = Table.TransformColumns(RemoveDots, {{"Normalized", each fnReplaceSuffix(_, "inc", " inc")}}),

    #"Capitalized Each Word" = Table.TransformColumns(HandleInc,{{"Normalized", Text.Proper}})
        
in
    #"Capitalized Each Word"

     

    So, while the M statement may be complicated, you do get a much simplifier model.

     

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

      Hello Data--yes, if the normalizations are predictable.  I should have been clearer that some systems have hundreds of facilities...and many of the transforms aren't cleanup of an entry but using the appropriate one.

       

      CHS/Community Health System

      Community Health System

      CHS

       

      All should become Community Health System.  Hence it's a custom thing rather than a transform.  Can you find/lookup and substitute a proper name during the query?

       

      Thanks,

      Tom

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

        So, create a function and then create a new column that calls that function with the value that you want to lookup and have the function return the correction. Here is an example from an upcoming blog post on Hex to Decimal conversion. You should be able to use the same basic process. Note, your list of lookups and corrections could come from a data source such as a SQL table instead of the list that I created manually in "M" code:

         

        let
    fnHex2Dec = (input) =>
 
let
 
values = {
 
{"0", 0},
{"1", 1},
{"2", 2},
{"3", 3},
{"4", 4},
{"5", 5},
{"6", 6},
{"7", 7},
{"8", 8},
{"9", 9},
{"A", 10},
{"B", 11},
{"C", 12},
{"D", 13},
{"E", 14},
{"F", 15}
 
},
 
Result = Value.ReplaceType({List.First(List.Select(values, each _{0}=input)){1}},type {number})
 
in
 
Result
in
    fnHex2Dec

        This function takes a single input parameter, a single text character and translates it to a decimal equivalent. You can test this function by clicking the "Invoke" button in the Power Query Editor window. Be sure to enter a single value preceded by a single quote, such as 'A. The single quote forces the input to be recognized as text. This ensures that if you enter a 7, that it is recognized as text instead of a number.

         

        One other note, you would have to add some logic for lookups that were correct, they would not be found so probably get a null result back or have to handle the error so you would have to wrap your call to this function in an "if" statement in the column code and if an error or null comes back, just use the original value. If I have some time, I'll play with it a little and see if I can get you even closer.