Not Like function

[TonyACT]

Hi All,

I’m new here and hope someone can help.

My problem is that I have an imported file with 150 plus columns with certain strings (in a separate table) I need to pull out. Unfortunately, the columns also include a lot of other data and text. The solution I have come up with is to merge the columns separating them with a “|”. So each row would look something like:

|Cat|Dog|Cow|Horse|

Then I remove anything that is not in my other Table, so it would look like this:

|Cat||Cow||

I can then use a function to remove the duplicate “|”. Leading me to:

|Cat|Cow|

What I can’t do is find a command that says, “Is Not Like”. Any pointers would be much appreciated, Thanks.

[TonyACT]

Wow, that is perfect. I can now have a go at using it on my data. It has also made me realise how far away from being competent in PQ I am. I have been recommended a book 'M is for Data Monkey book by Miguel Escobar and Ken Puls'. So I will be getting one of those and if you or anyone else could recommend other good books or web sites I would be more than happy to take your advice.

[jennratten]

Hello - if you are simply looking for a way to replace a list of values over a table, please give this function a try.  It also includes a reproducable example and helpful notes.  There are two options for the function - to search for the entire contents of the cell or to search for substrings.

• Create a new, blank query named fnFindReplaceOverEntireTable.  Copy the script below and paste it into the Advanced Editor.  Save.  
• Create another new, blank query with the name of your choice.  Copy the script for the reproducable example and paste it into the Advanced Editor.  Save.
• View the 2nd query you created. It includes three steps: ReplacementsTable, DataTable and PerformReplacements
• The goal of the function is to search the DataTable and replace all instances of the old values in the ReplacementsTable with the corresponding new values in the ReplacementsTable.
  
  Replacements Table

Data Table (Note the special characters in some columns )

Perform Replacements (for substrings).  When the third argument is "substring" the function performs all substring replacements.  When the third argument is "contents" no changes are made because no matches were found when searching the entire contents of cells.

Script for custom function:

// ****************************************************************************************************************/
//   fnFindReplaceOverEntireTable
// ****************************************************************************************************************/
//
//   PURPOSE
//   - Replace one or more values with a corresponding value in all text columns of a table.
//   - A replacements table is used to define old/new values to find/replace.
//
//   INPUTS
//   - DataTable (table): 
//         The table in which the replacements should be performed (aka the starting table)
//
//   - ReplacementsTable (table): 
//         A table containing two columns with the old and new values to find/replace.
//         The column names can be anything but column 1 must be the old values and column 2 must be the new values.
// 
//   - ReplacementsType (text): 
//         This argument is optional.
//         If it is omitted or if an invalid entry is made, a default of 'contents' is assigned.
//         Options are 'contents' and 'substring'.
//         contents: find/replace entire field values (default)
//         substring: find/replace substrings within field values
//  
//   DEVELOPER
//   - Jenn Ratten
//   - jennifer.ratten@nfp.com
//   - last revised: 6/24/2021
//
// ****************************************************************************************************************/
/*
REPRODUCABLE EXAMPLE

let

    ReplacementsTable = Table.TransformColumnTypes(
            Table.FromRows(
                Json.Document(
                    Binary.Decompress(
                        Binary.FromText("i45W0gFCpVidaKX4eBirDsaIgzEiIsCsWAA=", BinaryEncoding.Base64), 
                        Compression.Deflate
                    )
                ), 
                let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [old = _t, new = _t]
            ),
            {{"old", type text}, {"new", type text}}
        ),
    DataTable = Table.TransformColumnTypes(
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText("i45W8sgvLsgsScxR0lEKDQYSwfmlJRmpicUlQLabTx2QNDQwMDAE0n4BCj6uYa4+Cp4KoX7Bzh6uLgoBQf7O+sFhziCNRqbGSrE6BAzU8YEaaIRiIA4TzbCbGB+P6kiokcaoRuIw09yCsCthJpqgmBiG3UBLI+INNEU2ELt5xgbmOM2LiMAw0QxIuwaHKASEKPiE4YwaY0Ol2FgA", BinaryEncoding.Base64),
                    Compression.Deflate
                )
            ), 
            let _t = ((type nullable text) meta [Serialized.Text = true]) 
            in type table [#"Place of Service" = _t, Country = _t, Region = _t, State = _t, #"CDM NUMBER" = _t, #"GEN-DESC" = _t, PRICE = _t]
        ), {{"Place of Service", type text}, {"Country", type text}, {"Region", type text}, {"State", type text}, {"GEN-DESC", type text}, {"CDM NUMBER", Int64.Type}, {"PRICE", Currency.Type}}
    ),
    PerformReplacements = fnFindReplaceOverEntireTable ( DataTable, ReplacementsTable, "substring" )

in
    PerformReplacements
*/
// ****************************************************************************************************************/

let

    fn = ( DataTable as table, ReplacementsTable as table, optional ReplacementsType as text ) as table =>

let

    // Assign the ReplacementsType argument text to a variable, with error handling.
    // To prevent errors in the event an invalid string is entered...
    // If the text equals anything other than "substring" (even an invalid selection), "contents" will be applied.
    ReplacementsTypeSelection = 
        if Text.Lower(ReplacementsType) = "substring" 
        then "substring" 
        else "contents",

    // Assign replacer functions to variables.
    ReplacementsTypeContents = Replacer.ReplaceValue,             // entire contents
    ReplacementsTypeSubstring = Replacer.ReplaceText,             // substrings

    // Assign selected replacer function to a variable.
    ReplacerFunctionAsFunction = 
        if ReplacementsTypeSelection = "substring" 
        then ReplacementsTypeSubstring 
        else ReplacementsTypeContents,    

    // Perform the replacements.
    Output = List.Accumulate (                                    // loop through all rows in a table column (column of values = list)
        Table.ToRows ( ReplacementsTable ),                       // replacements table as a list of lists
        DataTable,                                                // table in which the replacements should be performed
        ( t, r ) =>                                               // declare state and current variables and inline function (data table and replacements rows)
            Table.ReplaceValue (                                  // function to perform the replacements
                t,                                                // the table before each iteration  
                r{0},                                             // old value to search for - column 1 of the replacements table (columns index at base 0) 
                r{1},                                             // new value to replace with - column 1 of the replacements table (columns index at base 0)
                ReplacerFunctionAsFunction,                       // variable with the replacer function definition 
                Table.ColumnsOfType ( t, { type nullable text } ) // only perform replacements in data table columns of type text
            ) 
    )
in
    Output,

    // Define a new function type that includes the necessary documentation as a metadata record.
    fnType = 
        type function (
            DataTable as (
                type table meta [
                    Documentation.FieldCaption = "Data Table",
                    Documentation.FieldDescription = "table in which replacements are performed",
                    Documentation.SampleValues = {"Table1", "Table2"}                    
                ]
            ), 
            ReplacementsTable as (
                type table meta [
                    Documentation.FieldCaption = "Replacements Table",
                    Documentation.FieldDescription = "table containing old/new values in columns 1 and 2",
                    Documentation.SampleValues = {"Table1", "Table2"}                       
                ]
            ), 
            optional ReplacementsType as (
                type text meta [
                    Documentation.FieldCaption = "Replacements Type Selection",
                    Documentation.FieldDescription = "Should the entire value or a substring searched/replaced?",
                    Documentation.AllowedValues = {"contents", "substring"}                     
                ]
            )
        ) as table meta [
            Documentation.Name = "fnFindReplaceOverEntireTable",
            Documentation.LongDescription = "This function returns a new table with text with replacements performed.",
            Documentation.Examples = 
            {
                [
                    Description = "This function returns a new table with text with replacements performed.",
                    Code = "fnFindReplaceOverEntireTable ( DataTable, ReplacementsTable, ""contents"" )",
                    Result = ""
                ]
            }
        ]
    in 
        Value.ReplaceType ( fn, fnType )

Reproducable Example

let

    ReplacementsTable = Table.TransformColumnTypes(
            Table.FromRows(
                Json.Document(
                    Binary.Decompress(
                        Binary.FromText("i45W0gFCpVidaKX4eBirDsaIgzEiIsCsWAA=", BinaryEncoding.Base64), 
                        Compression.Deflate
                    )
                ), 
                let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [old = _t, new = _t]
            ),
            {{"old", type text}, {"new", type text}}
        ),
    DataTable = Table.TransformColumnTypes(
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText("i45W8sgvLsgsScxR0lEKDQYSwfmlJRmpicUlQLabTx2QNDQwMDAE0n4BCj6uYa4+Cp4KoX7Bzh6uLgoBQf7O+sFhziCNRqbGSrE6BAzU8YEaaIRiIA4TzbCbGB+P6kiokcaoRuIw09yCsCthJpqgmBiG3UBLI+INNEU2ELt5xgbmOM2LiMAw0QxIuwaHKASEKPiE4YwaY0Ol2FgA", BinaryEncoding.Base64),
                    Compression.Deflate
                )
            ), 
            let _t = ((type nullable text) meta [Serialized.Text = true]) 
            in type table [#"Place of Service" = _t, Country = _t, Region = _t, State = _t, #"CDM NUMBER" = _t, #"GEN-DESC" = _t, PRICE = _t]
        ), {{"Place of Service", type text}, {"Country", type text}, {"Region", type text}, {"State", type text}, {"GEN-DESC", type text}, {"CDM NUMBER", Int64.Type}, {"PRICE", Currency.Type}}
    ),
    PerformReplacements = fnFindReplaceOverEntireTable ( DataTable, ReplacementsTable, "substring" )

in
    PerformReplacements

[TonyACT]

Unfortunately, I can’t show the data due to the nature of the data and my employer. I have had a think about my problem, and I think I can solve my problem by using a list of words to find in a table and an additional column with a function that looks up the word. I did try PositionOf, but it can only find a match if there is only a perfect match and not if it is part of the ‘cell’. Unless there is a way to change his to match part of a ‘cell’ of course.

List.PositionOf(Levels[LevelF],[Concat])

[TonyACT]

Thanks for the reply. I can't give example data due to the nature of the data and my employer, sorry. An example of what I am after would be, if I had a table that was say 150 columns and 150 rows and wanted to clear all 'cells' that do not match a list of words, how would I do it. In Excel I could use a formula to say <> or VBA to loop through and remove anything not on the list. In query I can't seem to find the equivalent.

After doing this I could then merge all columns and the result would be text separated by a “|” .I hope this makes more sense.

[ronrsnfld]

Without an example, it's a little hard to follow.

I think you want to blank cells in a table that are not also in your word list.

I set up two lists as below

Data

Words to find

Then this M code may do what you want.

Please read the code comments to understand the algorithm

We transform each column by, if the cell does not match any item in the desired word list, we set that cell to null

As written, it should be insensitive to the column names or the number of rows/columns

let

//read in the data table
    Source = Excel.CurrentWorkbook(){[Name="dataTbl"]}[Content],

// Create list of column Names
    colNames = Table.ColumnNames(Source),

//set all columns to type text
    dataTable = Table.TransformColumnTypes(Source,
        List.Transform(colNames, each {_, type text})),

//get list of desired words
//Assumes Column Name = "Words"
    Source2 = Excel.CurrentWorkbook(){[Name="wordTbl"]}[Content],
    wordList= Table.TransformColumnTypes(Source2, {"Words", type text})[Words],

//Blank the matches
    xForm = List.Transform(colNames, each {_, (c)=> if List.MatchesAny(wordList, each _ = c) then c else null} ), 

    result = Table.TransformColumns(dataTable, xForm)
    
in
    result

Result

[ronrsnfld]

You could create a dummy data set that illustrates the problem; and also shows exactly what you expect for a result.

[AlexisOlson]

I don't quite follow. What's the next step you need "Is Not Like" for?

Some example data along with the final desired result would be useful.

[TonyACT]

Unfortunatley I cannot use the actual data due to my work etc, sorry. But I have a created basic tables of what I am trying to achieve. As you can see, the first table is the raw data, the Pets table is a list of pets and the third is the first table with a merged Column with “|” delimiter. The Result Column is what I am trying to achieve, by either looking up the pets and returning the matched word. Or removing all words that are not in the Pets table. Any duplicate "|" can be removed with a function.

I hope that makes more sense.

[TonyACT]

Sorry, but I am having a few Internet problems, so I am not ignoring the advice. Thanks

[AlexisOlson · Accepted solution]

You can do this in a single step with a custom column.

Text.Combine(List.Intersect({Record.ToList(_), Pets[Pets]}), "|")

Full sample query you paste into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKslIVVBITixRKE4sUdJRcslPB5K5lQoZ+aXFqUqxOtFQofyy1CKFxLwUBRADyHcGKkfIOoM1+1YqpCcWpaTmgWU88ouAJsDkXPKB6mMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Text.Combine(List.Intersect({Record.ToList(_), Pets[Pets]}), "|"), type text)
in
    #"Added Custom"

[TonyACT]

Wow, that is perfect. It has also made me realise how far away from being competent in PQ I am. I have been recommended a book 'M is for Data Monkey book by Miguel Escobar and Ken Puls'. So I will be getting one of those and if you or anyone else could recommend other good books or web sites I would be more than happy to take your advice.

[AlexisOlson]

I've heard good things about that book too. An updated version came out relatively recently, so make sure to get the 2nd edition.

I also recommend this blog series:
https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi...