Forum Discussion

campelliann's avatar
campelliann
Post Patron
4 years ago
Solved

In Operator in power Query

Hi,   I want to make a custom filter if a column has certain values: If column = "A" or column = "B" or column ="C" then "filter" else "not filter". Is there an IN operator, if column IN {"A","B...
Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.  You can use List.Contains( ) to do it.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Letter = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if List.Contains({"A", "B"}, [Letter]) then "Y" else "N")
in
    #"Added Custom"

     

    Pat

     

Jestentime's avatar
Jestentime
New Member
2 years ago

how are you populating the table with  A B C & D? I have figured out almost everything else from your M-Code but that.

your code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Letter = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if List.Contains({"A", "B"}, [Letter]) then "Y" else "N")
in
    #"Added Custom"