Forum Discussion

nickvanmaele's avatar
nickvanmaele
Advocate II
3 years ago
Solved

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

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 i...
Show and Tell
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    3 years ago

    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

     

AlienSx's avatar
AlienSx
Super User
3 years ago

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's avatar
    nickvanmaele
    Advocate II
    3 years ago

    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"