Update rows in a table based on other rows in that same table

[nickvanmaele]

Hi, 

I wanted to share a solution that I created for the following problem. 

If anyone has a more elegant solution, feel free to comment. 

Business problem description. 

An IT environment in a company has several test instances called "DEVx", where x is a number. For example, DEV1, DEV2, ...

For internal audit, access of people to the DEVx environments will be checked. All people having roles in the production system called "PROD" should automatically be approved to have those same roles in all "DEVx" environments. 

Input

For every environment, an extract file is available which details every role of every person. All extracted files for all environments are appended into one table, which shows all roles available to all people in all environments. 

In this post, I will use a simple sample table as follows: 

Environment	Role	Person
DEV1	Accountant	David
DEV1	Manager	Goliath
PROD	Manager	Goliath

Output

Based on the above input, we want to obtain the following approval table for use in internal audit checks. 

Environment	Role	Person	Approved?
DEV1	Accountant	David	FALSE
DEV1	Manager	Goliath	TRUE
PROD	Manager	Goliath	TRUE

Since Goliath is a Manager in PROD, he can also be approved as manager in DEV1. 

Since David has no role in PROD, he cannot be auto-approved in DEV1 and must be flagged. 

Solution steps

Step 1: Read the input table into Power Query and add two additional columns, one with a unique key <Environment>-<Role>-<Person> and one with a RolePersonKey <Role>-<Person>, where the "-" sign is added for readability of the key. 

Step 2: Create a table "ProdTable" and filter on only the roles available in PROD.

Step 3: Create a table with all the approved roles across all environments

First reference the InputTable and add a column "Approved?" which can contain FALSE or TRUE. 

If a person has a role in PROD in Step 2, he can have that role in any environment. In other words, the same Role-Person combination can be set to TRUE for all environments, so the environment is irrelevant in selecting the rows.

To model this, use the RolePersonKey in InputTable and ProdTable in a merge query with an inner join.

Rename the merge query to "Approved".

All rows in the "Approved" table can have the value of column "Approved?" set to TRUE. 

Step 4: Create the opposite of step 2, i.e., create a table NotApproved which keeps only the rows of InputTable that do NOT appear in the Approved table. Set the value of column "Approved?" to FALSE. 

Step 5: Append the Approved and NotApproved tables to reveal the Approval status TRUE or FALSE for all people in all environmments. 

M query codes

Query 1: Read the Excel input table and add two key columns:

let
    Source = Excel.CurrentWorkbook(){[Name="tblInput"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Environment", type text}, {"Role", type text}, {"Person", type text}}),
    #"Added Unique Key" = Table.AddColumn(#"Changed Type", "UniqueKey", each Text.Trim([Environment]) & "-" & Text.Trim([Role]) & "-" & Text.Trim([Person])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Unique Key",{{"UniqueKey", type text}}),
    #"Added RolePersonKey" = Table.AddColumn(#"Changed Type1", "RolePersonKey", each Text.Trim([Role]) & "-" & Text.Trim([Person])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added RolePersonKey",{{"RolePersonKey", type text}})
in
    #"Changed Type2"

Query 2: Filter only on the roles assigned in PROD

let
    Source = pqInputTable,
    #"Filtered Environment PROD" = Table.SelectRows(Source, each ([Environment] = "PROD"))
in
    #"Filtered Environment PROD"

Query 3: Create the table of all approved roles in all environments (via an Inner Join on RolePersonKey)

let
    Source = pqInputTable,
    #"Added Custom" = Table.AddColumn(Source, "Approved?", each "FALSE"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Approved?", type logical}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"RolePersonKey"}, pqProdTable, {"RolePersonKey"}, "pqPRODRolePerson", JoinKind.Inner),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Queries",false,true,Replacer.ReplaceValue,{"Approved?"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"pqPRODRolePerson"})
in
    #"Removed Columns"

Query 4: Create the table with all roles that are NOT appearing in PROD and are NOT approved (Left Anti Join on UniqueKey)

let
    Source = Table.NestedJoin(pqInputTable, {"UniqueKey"}, pqApproved, {"UniqueKey"}, "pqEnvRolePersonApproved", JoinKind.LeftAnti),
    #"Added Custom" = Table.AddColumn(Source, "Approved?", each "FALSE"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"pqEnvRolePersonApproved"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Approved?", type logical}})
in
    #"Changed Type"

Query 5: Append the Approved and NotApproved tables

let
    Source = Table.Combine({pqNotApproved, pqApproved}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"UniqueKey", "RolePersonKey"})
in
    #"Removed Columns"

Screenshot of Excel

[AlienSx · Accepted solution]

Hi, @nickvanmaele 

let
    Source = Excel.CurrentWorkbook(){[Name="tblInput"]}[Content],
    g = Table.Group(Source, {"Person"}, {{"rows", each _}, {"Approved?", (x) => List.Contains(x[Environment], "PROD")}}),
    expand = Table.ExpandTableColumn(g, "rows", {"Environment", "Role"})
in
    expand

[nickvanmaele]

Hi @AlienSx ,

Wow, much more elegant code, that is for sure. Thanks for posting. 

When I tested both our codes on a slightly different InputTable, your code generated one line set to TRUE that should be set to FALSE, as indicated in orange below: since Goliath does not have the role of Receptionist in PROD,  his role of Receptionist in DEV7 should not be approved.

Your code groups by Person only. When I rework it a little to work with (Person and Role), it worked great. 

Thanks so much for pointing me towards this more elegant method. 

The modified code is below. 

let
    Source = Excel.CurrentWorkbook(){[Name="tblInput"]}[Content],
    Group = Table.Group(Source, {"Person", "Role"}, {{"rows", each _}, {"Approved?", (x) => List.Contains(x[Environment], "PROD")}}),
    Expand = Table.ExpandTableColumn(Group, "rows", {"Environment"}),
    #"Reordered Columns" = Table.ReorderColumns(Expand,{"Environment", "Role", "Person", "Approved?"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Environment", Order.Ascending}, {"Role", Order.Ascending}, {"Person", Order.Ascending}})
in
    #"Sorted Rows"