Custom Column that Checks for Matches in Another Row

[Drea]

I'm trying to add a check for reliability between ratings given by coordinators and supervisors, when all the ratings are in the same column, and another column lists the rater's role, and a series of other columns specify that they are of the same person in the same time and context.

The easiest way to do this would be to pivot the ratings row into columns by role and then create a simple conditional column, but I want the reliability test integrated into a matrix visualization that does a lot else and depends on the ratings staying in one column.

I'm hoping to achieve this in Power Query, but maybe it needs a Measure in DAX.

The logic is roughly this:

if rater_role = "Supervisor"

  then if (row exists where ID=ID and FY=FY and Department=Department and Month=Month and Level=Level and Rating_Criterion=Rating_Criterion and Rater_Role = "Coordinator"

  then if (Rating=Rating 1 else 0))

  else [blank]

So adding values only to Supervisor rows, if a row exists where all values are identical except that the rater is Coordinator, test if the Rating matches.  If it does, 1, if it doesn't, 0.  For all non-supervisor rows, or supervisor rows where no row with a matching Coordinator rating, blank.

I've been searching the forums and haven't found a case that addresses quite what I'm trying to do here.  Thank you all for any guidance you can provide!

ID	FY	Department	Month	Level	RATER_ROLE	RATING_CRITERION	RATING	DESIRED_ROW
1	2122	EC	January	2	Coordinator	1	3
1	2122	EC	January	2	Supervisor	1	3	1
1	2122	EC	January	2	Coordinator	2	3
1	2122	EC	January	2	Supervisor	2	2	0
2	2122	EC	January	1	Coordinator	3
2	2122	EC	January	1	Supervisor	3	3
2	2122	EC	January	1	Coordinator	4	3
2	2122	EC	January	1	Supervisor	4	3	1
3	2122	SA	February	2	Coordinator	5	3
3	2122	SA	February	2	Supervisor	5	1	0
3	2122	SA	February	2	Supervisor	6	3

[Drea · Accepted solution]

Thank you, Yolo!  Although this is producing an odd result!

My source is actually an Oracle database through an SQL query.  Adding the code for the #"Added Custom" does indeed produce preview data where I'm seeing 1 or 0 beside Supervisor rows and blanks next to Coordinator.  But when I apply and close, the refresh on the query begins pulling many, many more rows than actually exist in the data.  So something about this #"Added Custom" is producing many extra rows rather than only adding a column with 1,0, or null next to existing data.  It's so many rows that the query inevitably crashes before completing--which is generally *not* an issue with our database, even when handling extremely large queries.  But there shouldn't be any additional rows created at all, so whatever you might say could be an issue with our database, we have an issue with the query.

Here's what I'm adding.  The query already has commands for changing types and correcting errors, so what appears above differs slightly from what you posted, but it achieves the same goal and retrieves the correct number of rows before the following is added.

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=if [RATER_ROLE]="Supervisor" then Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[ID]=[ID] and x[FY]=[FY] and x[Department]=[Department] and x[Month]=[Month] and x[Level]=[Level] and x[RATING_CRITERION]=[RATING_CRITERION] and x[RATER_ROLE]="Coordinator" and  x[RATING]=null )) else null,
b=if [RATER_ROLE]="Supervisor" then Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[ID]=[ID] and x[FY]=[FY] and x[Department]=[Department] and x[Month]=[Month] and x[Level]=[Level] and x[RATING_CRITERION]=[RATING_CRITERION] )) else null,
c=if [RATER_ROLE]="Supervisor" then Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[ID]=[ID] and x[FY]=[FY] and x[Department]=[Department] and x[Month]=[Month] and x[Level]=[Level] and x[RATING_CRITERION]=[RATING_CRITERION] and x[RATER_ROLE]="Coordinator" and  x[RATING]=[RATING] )) else null
in if [RATER_ROLE]<>"Supervisor" or ([RATER_ROLE]="Supervisor" and (b=1 or a>0)) then null else if [RATER_ROLE]="Supervisor" and c>0 then 1 else 0)
in
    #"Added Custom"

[v-xinruzhu-msft]

Hi @Drea 

You can put the follwing code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyNDICUq7OQMIrMa80sagSJArEzvn5RSmZeYkl+UVAHkitsVKsDiFdwaUFqUVlmcUkaUK1yogcq4zAGKTJCKcmQwyrjIFYgQhdKFYZw91HilUmROpCsQqhyRihKdgRSLilJhXhDEFTYrWhWGYKdgGpuswgdsUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, FY = _t, Department = _t, Month = _t, Level = _t, RATER_ROLE = _t, RATING_CRITERION = _t, RATING = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"FY", Int64.Type}, {"Department", type text}, {"Month", type text}, {"Level", Int64.Type}, {"RATER_ROLE", type text}, {"RATING_CRITERION", Int64.Type}, {"RATING", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=if [RATER_ROLE]="Supervisor" then Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[ID]=[ID] and x[FY]=[FY] and x[Department]=[Department] and x[Month]=[Month] and x[Level]=[Level] and x[RATING_CRITERION]=[RATING_CRITERION] and x[RATER_ROLE]="Coordinator" and  x[RATING]=null )) else null,
b=if [RATER_ROLE]="Supervisor" then Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[ID]=[ID] and x[FY]=[FY] and x[Department]=[Department] and x[Month]=[Month] and x[Level]=[Level] and x[RATING_CRITERION]=[RATING_CRITERION] )) else null,
c=if [RATER_ROLE]="Supervisor" then Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[ID]=[ID] and x[FY]=[FY] and x[Department]=[Department] and x[Month]=[Month] and x[Level]=[Level] and x[RATING_CRITERION]=[RATING_CRITERION] and x[RATER_ROLE]="Coordinator" and  x[RATING]=[RATING] )) else null
in if [RATER_ROLE]<>"Supervisor" or ([RATER_ROLE]="Supervisor" and (b=1 or a>0)) then null else if [RATER_ROLE]="Supervisor" and c>0 then 1 else 0)
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.