Filter out certain rows based on values in 2 columns

[tgjones43]

Hi all

I'm hoping someone can suggest some M code that can do the following.

For each unique value in the [Number] column, apply the following rules:

If all values in the [Value] column are "A" only, keep all rows

If all values in the [Value] column are "B" only, keep all rows

If the values in the [Value] column are a mix of "A" and "B", keep all rows of "A" only

I have tried to show how this would work in the table below, with the final column being the desired outcome.

Number	Value	Keep
1	B	Yes
1	B	Yes
2	A	Yes
2	B	No
3	A	Yes
3	A	Yes
3	B	No
4	A	Yes
5	B	Yes

Thank you so much!

[DataInsights · Accepted solution]

@tgjones43,

Try this solution in Power Query. It uses Group By and a custom column.

M code:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMlTSUXJSitVBZhkBWY5wFkTMGC6GyoLImsDFTCFisQA=", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Number = _t, Value = _t]
  ),
  ChangeType = Table.TransformColumnTypes(Source, {{"Number", Int64.Type}}),
  GroupRows = Table.Group(
    ChangeType,
    {"Number"},
    {
      {"Distinct Count", each Table.RowCount(Table.Distinct(_)), Int64.Type},
      {"All", each _, type table [Number = nullable number, Value = nullable text]}
    }
  ),
  ExpandAll = Table.ExpandTableColumn(GroupRows, "All", {"Value"}, {"Value"}),
  AddColumn = Table.AddColumn(
    ExpandAll,
    "Keep Column",
    each
      if [Distinct Count] = 1 then
        "Y"
      else if [Distinct Count] = 2 and [Value] = "A" then
        "Y"
      else
        "N"
  )
in
  AddColumn

Result:

[tgjones43]

Thank you so much @DataInsights, that is a great solution!