locked
Replace full string if contains a substring from a list RRS feed

  • Question

  • Hi

    I would like to simplify my data to provide more meaningful grouping  e.g if [Description] contains the string 'Month', replace the entire string with 'New Month String', if Description contains 'Annual' replace the entire string with 'New Annual String'.  Please see attached.

    I can see examples of how to replace a string in a string but not how to replace the whole string.

    Thanks for your help

    Thursday, March 29, 2018 10:34 AM

Answers

  • Some time ago, I created a function to duplicate the DAX LOOKUPVALUE function. In M nomenclature, I named the function Table_LookupValue. Since you are free to choose the lookup criteria, it turns out to be more flexible than LOOKUPVALUE.

    Create a query named Table_LookupValue and paste in the following code:

    (table as table, resultColumnName as text, lookupCriteria as function) as any =>
    let
        FilteredRows = Table.SelectRows(table, lookupCriteria),
        DistinctValues = List.Distinct(Table.Column(FilteredRows, resultColumnName)),
        LookupValue = try if List.Count(DistinctValues) > 1 then error "No unique value found"
                             else DistinctValues{0}
                      otherwise error "No matching value found"
    in
        LookupValue

    Assuming that the name of the Replacement table is Replacements (substitute with actual name), you can add the following column to the OriginalString table:

    let
        <PreviousTableStepName> = ...,
        TransformedColumn = 
            Table.TransformColumns(
                <PreviousTableStepName>, 
                { "OrigString", 
                  each try Table_LookupValue(
                               Replacements, 
                               "ReplaceWith", 
                               (i) => Text.Contains(_, i[strContains], Comparer.OrdinalIgnoreCase)
                           ) otherwise _ 
                }                 
            )
    in
        TransformedColumn
    Note: If you replacement table is large, you should buffer the table in a step prior to the TransformedColumn step.


    Thursday, March 29, 2018 5:39 PM