Forum Discussion
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:
| 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.
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.
1 Reply
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.