Remove rows by a Criteria

[antalgu]

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:

Campaign	Call duration
A	10
A	20
B	10

I want to eliminate the calls that have a duration < 15 and are of campaign A, so the resulting table would look like this:

Campaign	Call duration
A	20
B	10

So what I really wanna do is eliminate columns based on a criteria, I didn't seem to find any function capable of doing that at first glance, but I suspect it could be done using the function Table.RemoveMatchingRows, but I don't know how.

[edhans · Accepted solution]

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.