Forum Discussion
Need help excluding certain rows based on value in another column
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIB4rzSnBwgZWSgFKsTrWQEFTVECBkDmY6oQiZoeg3BoqZApj8QmyCEzNAUmoBFzYFMZyRRC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OfferID = _t, Status = _t, OriginalOfferID = _t, ClientID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OfferID", Int64.Type}, {"Status", type text}, {"OriginalOfferID", Int64.Type}, {"ClientID", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([OriginalOfferID] = null) and ([Status] = "D")), #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each Table.RowCount(Table.SelectRows(#"Changed Type", (inner)=> inner[Status]<>[Status] and inner[OriginalOfferID]=[OfferID]))=0) in #"Filtered Rows2"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
I have no idea what you are doing. Works fine on my end. See attached
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AlB,
Thx for your response. Let me expand the table a bit.
| OfferID | Status | OriginalOfferID | ClientID |
| 1 | D | null | 20 |
| 2 | D | 1 | 20 |
| 3 | A | 1 | 20 |
| 4 | D | null | 21 |
| 5 | O | 4 | 21 |
| 6 | D | null | 24 |
| 7 | C | null | 28 |
In this case I only want offer 6 to be counted as 'Denied' as this offer doesn't have any follow-up offer.
- Offer 1,2,3 are practically the same offer, however, because of some mistakes they had to be markes denied and a new version was created.
- Offer 4,5 cannot be counted as Denied because a copy of the offer is still outstanding. Only when this one would also be denied it can count as a Denied offer.
- Offer 7 is still in Concept status
I do want this in PQ yes.
Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIB4rzSnBwgZWSgFKsTrWQEFTVECBkDmY6oQiZoeg3BoqZApj8QmyCEzNAUmoBFzYFMZyRRC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OfferID = _t, Status = _t, OriginalOfferID = _t, ClientID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OfferID", Int64.Type}, {"Status", type text}, {"OriginalOfferID", Int64.Type}, {"ClientID", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([OriginalOfferID] = null) and ([Status] = "D")), #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each Table.RowCount(Table.SelectRows(#"Changed Type", (inner)=> inner[ClientID] = [ClientID] and inner[Status]<>[Status]))=0) in #"Filtered Rows2"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers