Inspect records in Group By table for values and return conditional value based on result

[Anonymous]

Once you perform a group by on a table and have a new column that holds all the aggregated data as a table object, is it possible to inspect the contents of that aggregated data, do some if/then logic, and return a value in a new column based on the results?  At first I was looking at a few custom columns, but I couldn't figure out the right logic for that column definition to make it dig into the grouped data once I group by PC Name.
 
I have a small sample example below.  Essentially I have a messy table of all software installed across PC's.  I'm trying to find which machines have some version of "ArcGIS Desktop" installed, and which have "ArcGIS Pro" installed. 
  PC Name Application BUILD OS Application Category Version

KVWLK	ArcGIS Desktop 10.6.1	10.6.9270	Windows 7	Desktop	10.6.1
PRWTV	ArcGIS 10.4.1 for Desktop	10.4.5686	Windows 10	Desktop	10.4.1
KVWLK	ArcGIS Pro	2.4.19948	Windows 7	Pro	null
BLTOR	ArcGIS Pro	2.5.22081	Windows 10	Pro	null
GRMTP	ArcGIS Desktop 10.5.1	10.5.7333	Windows 10	Desktop	10.5.1
PRWTV	ArcGIS Workflow Manager 10.4.1 for Desktop	10.4.7146	Windows 10	Other	10.4.1
PRWTV	ArcGIS Data Reviewer 10.4.1 for Desktop	10.4.862	Windows 10	Other	10.4.1

And  the result I want is this:

PC Name OS ArcGIS Desktop Version ArcGIS Desktop Build ArcGIS Pro Version ArcGIS Pro Build Other Installs

KVWLK	Windows 7	10.6.1	10.6.9270	2.4	2.4.19948	null
GRMTP	Windows 10	10.5.1	10.5.7333	Not Installed	Not Installed	null
BLTOR	Windows 10	Not Installed	Not Installed	2.5	2.5.22081	null
PRWTV	Windows 10	10.4.1	10.4.5686	Not Installed	Not Installed	ArcGIS Workflow Manager 10.5.1 for Desktop ArcGIS Workflow Manager 10.5.1 for Desktop

In pseudo code here's what I'm trying to do:
For each PC:
 Create Column for ArcMap Version
  Loop through aggregated data for this row
   If row WHERE "Application Category" = "Desktop"
    THEN return "Version" value from the aggregated row

 Create Column for Pro Version
  Loop through aggregated data for this row
   If row WHERE "Application Category" = "Pro"
    THEN return "Version" value from the aggregated row

 Create Colomn for Other Software
  Loop through aggregated data for this row
   For each row WHERE "Application Category" = "Other"
    concatenate values together and return

Any help would be appreciation,  

-Andrew

[artemus · Accepted solution]

This is pretty simple, assuming your table column is "Grouped Rows" your custom column for ArcMap Version would be:

= [Grouped Rows]{[Application Category = "Desktop"}]?[Version]?

Note: Will give error if sub table has multiple Application Categories with the same value.

[Anonymous]

This ended up working for me..or at least got me close enough to understand the logic of the curly brackets.  Just to clarify, below is the syntax that worked for me:

Adding the "ArcMap Version" column that holds the version or build value from the table object:

ArcMap Version = try [data]{[Application Category = "Desktop"]}[Version]
otherwise "None"

Adding the "Other Version" column to store a list of all the other software:

Step 1:  Add custom column that holds a list of the values with this formula: = Table.AddColumn(#"Added Custom3", "Other GIS Intalls", each Table.ToList(Table.SelectColumns(Table.SelectRows([data],each [Application Category] = "Other"),{"Application"})))

Step 2:  Expand the list using this formula (or clicking the expand button on the list's column header in the GUI:

= Table.TransformColumns(#"Added Custom4", {"Other GIS Intalls", each Text.Combine(List.Transform(_, Text.From), "#(cr)"), type text})

-Andrew

[Anonymous]

the final goal is not completely clear to me, but let try to use this scheme (is substantially a group by inside another group By)

    rr= Table.Group(yourTab, {"PC"},  {"cat", each Table.Group(_,{"Application Category"},{"dpo", (pc)=> pc[Application]})}),
    te= Table.ExpandTableColumn(rr, "cat", {"Application Category", "dpo"}, {"cat.Application Category", "cat.dpo"}),
    ve = Table.TransformColumns(te, {"cat.dpo", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    ve

You obviously have to adapt and complete the code.
Let me know if you find it suitable and sufficient to continue your analysis.