Selecting grouped data where a field value equals that of an ungrouped field

[fuzzyhobbit]

I have a table where I have grouped data by a single field (JobId) in the column "GroupedData". I am trying to select rows from the GroupedData column that meets several criteria but I am struggling with referencing a field outside of the GroupedData.

JobId	Revision	JobComplete	OperationCount	GroupedData
3653	1	FALSE	14	Table
3651	0	FALSE	1	Table

The GroupedData table has about 15 columns, one of which is "operation_revision". I am trying to use Table.SelectRows to get rows where 'operation_revision' in the GroupedData equals the 'Revision' value from the parent? table.

My current query is as follows but does not select any rows:

= Table.AddColumn(#"Renamed Columns", "InCompleteOperations", each Table.SelectRows([GroupedData], each [OperationComplete] = false and [operation_revision] = #"Renamed Columns"[Revision]))

I managed to get it working for a specific 'JobId' using the following query but I am unsure how to make it dynamic so that it works on each row:

= Table.AddColumn(#"Renamed Columns", "InCompleteOperations", each Table.SelectRows([GroupedData], each [OperationComplete] = false and [operation_revision] = #"Renamed Columns"{[JobId=3653]}[Revision]))

Can anyone point me in the direction of the correct syntax to use to accomplish this? TIA.

[ImkeF · Accepted solution]

Hi @fuzzyhobbit 

there's some syntax sugar involved in the code that is created by the engine by default. It hides what's really going on here.

The code that should work looks like so:

= Table.AddColumn(
      #"Renamed Columns",

      "InCompleteOperations",

      (outerFunction) => 
      Table.SelectRows(

              outerFunction[GroupedData],

              (innerFunction) => 
              innerFunction[OperationComplete] = false
              and innerFunction[operation_revision] = outerFunction[Revision]))

Here I've replaced the syntactic sugar of the "each" with 2 explicit function definitions. To use them, you HAVE to use their parameter names, making it a bit more transparent what's really going on. 

What might also be helpful to know is that in both M-functions (Table.AddColumn and Table.SelectRows), the (single) function parameters in the third arguments will always be the current row/record of the table. So you can give it any name you want, it will always be the current row that that will be represented by the parameter expression.

[fuzzyhobbit]

Hi @ImkeF ,

Thank you so much for your explanation. What you provided is much easier to understand than the manual you linked to.

I've replaced the `each` calls with a function and the query is now working as I expect. Kudos given!

[Greg_Deckler]

If anyone can, @ImkeF can.