Forum Discussion

antalgu's avatar
antalgu
Helper I
5 years ago
Solved

Remove rows by a Criteria

I'm pretty new to the M language of Power Query. I'm working with a table that has phone calls and information about each one of them, for what we want it that table could be summarized as this table...
  • edhans's avatar
    5 years ago

    You want Table.SelectRows. Start by removing all rows that are 15 or lower.

    That generates this statement. It is wrong, but Power Query has generated most of what we need.

    = Table.SelectRows(#"Changed Type", each [Call duration] <= 15)

    Now manually modify that to be this:

    = Table.SelectRows(#"Changed Type", each not ([Call duration] <= 15 and [Campaign] = "A"))

    That returns this:

    So what I told it is select any records with call durations that are less than 15 and  are of Campaign A. That is this part:

    ([Call duration] <= 15 and [Campaign] = "A")

    Since that is the exact opposite of what I really want, I put a "not" in front if it.

     

    Make sense? Full code here:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UIrVgTCNIEwnqGgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Campaign = _t, #"Call duration" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign", type text}, {"Call duration", Int64.Type}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each not ([Call duration] <= 15 and [Campaign] = "A"))
    in
        #"Filtered Rows"

     

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.