Forum Discussion

tgjones43's avatar
tgjones43
Icon for Helper IV rankHelper IV
5 years ago
Solved

Filter out certain rows based on values in 2 columns

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.

 

NumberValueKeep
1BYes
1BYes
2AYes
2BNo
3A

Yes

3AYes
3BNo
4AYes
5BYes

 

Thank you so much!

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    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:

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    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: