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


  • 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 =>
        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"

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

        <PreviousTableStepName> = ...,
        TransformedColumn = 
                { "OrigString", 
                  each try Table_LookupValue(
                               (i) => Text.Contains(_, i[strContains], Comparer.OrdinalIgnoreCase)
                           ) otherwise _ 
    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