keep rows conundrum

[Ushkamour]

Hi, I've been struggling with this and hope esomeone can help. I have a table of several thousand records. In this snip I've removed columns to simplify and anonymise. I want to remove rows to retain only those where there is a variance between consentglobalname for that contactnumber. In this snip, a suitable solution would leave me with only 4 rows, for contactnumber 'CON-383780'. I think the solution will use Group By but I can't figure it out. Any ideas?  Thanks in advance

[Ushkamour]

thanks @ronrsnfld  and @jgeddes thats exactly what I needed. Much appreciated

[ronrsnfld]

I suggest:

•  Group by py3_contactnumber
  • All Rows
  • Add custom aggregation to detect if both "No" and "Yes" are in the py3_consentglobalname column
  • Select only the rows that return true
  • Re-expand

let

//Replace Source line with your actual data table
    Source = Table,
    #"Grouped Rows" = Table.Group(Source, {"py3_contactnumber"}, {
        {"All", each _, type table [py3_consentglobalname=nullable text, py3_contactnumber=nullable text]},
        {"Select", each List.ContainsAll([py3_consentglobalname],{"Yes","No"}), type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Select] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"py3_contactnumber", "Select"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", 
        {"py3_consentglobalname", "py3_contactnumber"})
in
    #"Expanded All"

Original Table

Result

[jgeddes · Accepted solution]

You can group by py3_contactnumber choosing to aggregate by count distinct rows and keeping all rows. Then amend the resulting code to take the distinct count of only the py3_consentglobalname column. (The code would like this...)

= Table.Group(#"Changed Type", {"py3_contactnumber"}, {{"_nested", each _, type table [py3_consentglobalname=nullable text, py3_contactnumber=nullable text]}, {"Count", each Table.RowCount(Table.Distinct(Table.SelectColumns(_, "py3_consentglobalname"))), Int64.Type}})

You can now filter the resulting contactnumber count column so that only contacts with a count greater than one are kept.

From there you have to decide which rows you are keeping. If you are keeping only rows with consent as "yes" you could use Table.TransformColumns() to filter the nested tables to keep "Yes" rows. Then expand the tables.
Complete code would look like this...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtJRcvb30zWxMDY2sFSK1cEh6JdPpEKEoKmRiaGFAapuGoohu8bSzMwEtyDCLwYm5ugm4hOLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [py3_consentglobalname = _t, py3_contactnumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"py3_consentglobalname", type text}, {"py3_contactnumber", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"py3_contactnumber"}, {{"_nested", each _, type table [py3_consentglobalname=nullable text, py3_contactnumber=nullable text]}, {"Count", each Table.RowCount(Table.Distinct(Table.SelectColumns(_, "py3_consentglobalname"))), Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] = 2)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"}),
    Custom1 = Table.TransformColumns(#"Removed Columns", {{"_nested", each Table.SelectRows(_, each [py3_consentglobalname] = "Yes")}}),
    #"Expanded _nested" = Table.ExpandTableColumn(Custom1, "_nested", {"py3_consentglobalname"}, {"py3_consentglobalname"})
in
    #"Expanded _nested"

In this example I started with...

and ended with...

Hope this gets you pointed in the right direction.