Remove rows by a Criteria

antalgu — Fri, 02 Oct 2020 14:55:13 GMT

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.

Re: Remove rows by a Criteria

edhans — Fri, 02 Oct 2020 15:46:03 GMT

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.

topic Re: Remove rows by a Criteria in Power Query

Remove rows by a Criteria

Re: Remove rows by a Criteria