Forum Discussion

TonyACT's avatar
TonyACT
Frequent Visitor
4 years ago
Solved

Not Like function

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 co...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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's avatar
TonyACT
Frequent Visitor
4 years ago

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's avatar
    ronrsnfld
    Super User
    4 years ago

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

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    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