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