Creating a Rank Column within nested groups with Power Query

[rayza]

Hi, I am having trouble creating a ranked colum inside a nested group. I am not sure my approach to is this problem is correct. 

Description: I have a Query that runs daily and appends the results to a file. For each CollectionDate I wish to group each computerDnsName, then sort by lastSeen Descending, then add a Rank column.  So if I were to Filter the Rank to "1" we would have the most recent lastSeen entry for each computer per CollectionDate. 

Example of expected output:

computerDnsName	lastSeen	CollectionDate	GUID	exposureLevel	Rank
Workstation1191	3/07/2023 2:58	9-Aug-23	287237c4cf4f4c0	None	1
Workstation1191	29/06/2023 3:56	9-Aug-23	fdfe79636a1afd7	None	2
Workstation1191	27/06/2023 0:39	9-Aug-23	26e05b46e31397b	None	3
Workstation1191	26/06/2023 7:58	9-Aug-23	1598a46d5564412	None	4
Workstation1135	9/08/2023 0:40	9-Aug-23	2b7c781977cd4a7	Medium	1
Workstation1135	22/06/2023 2:37	9-Aug-23	b0c89945cfa2f4b	None	2
Workstation1135	2/03/2023 1:09	9-Aug-23	46b83af427e0783	None	3
Workstation1191	3/07/2023 2:58	8-Aug-23	287237c4cf4f4c0	None	1
Workstation1191	29/06/2023 3:56	8-Aug-23	fdfe79636a1afd7	None	2
Workstation1191	27/06/2023 0:39	8-Aug-23	26e05b46e31397b	None	3
Workstation1191	26/06/2023 7:58	8-Aug-23	1598a46d5564412	None	4
Workstation1135	7/08/2023 23:55	8-Aug-23	2b7c781977cd4a7	Medium	1
Workstation1135	22/06/2023 2:37	8-Aug-23	b0c89945cfa2f4b	None	2
Workstation1135	2/03/2023 1:09	8-Aug-23	46b83af427e0783	None	3
Workstation1191	3/07/2023 2:58	7-Aug-23	287237c4cf4f4c0	None	1
Workstation1191	29/06/2023 3:56	7-Aug-23	fdfe79636a1afd7	None	2
Workstation1191	27/06/2023 0:39	7-Aug-23	26e05b46e31397b	None	3
Workstation1191	26/06/2023 7:58	7-Aug-23	1598a46d5564412	None	4
Workstation1135	6/08/2023 23:36	7-Aug-23	2b7c781977cd4a7	None	1
Workstation1135	22/06/2023 2:37	7-Aug-23	b0c89945cfa2f4b	None	2
Workstation1135	2/03/2023 1:09	7-Aug-23	46b83af427e0783	None	3
Workstation1191	3/07/2023 2:58	4-Aug-23	287237c4cf4f4c0	None	1
Workstation1191	29/06/2023 3:56	4-Aug-23	fdfe79636a1afd7	None	2
Workstation1191	27/06/2023 0:39	4-Aug-23	26e05b46e31397b	None	3
Workstation1191	26/06/2023 7:58	4-Aug-23	1598a46d5564412	None	4
Workstation1135	26/06/2023 1:21	4-Aug-23	2b7c781977cd4a7	None	1
Workstation1135	23/06/2023 0:16	4-Aug-23	16b14e2f99a9d50	None	2
Workstation1135	22/06/2023 4:49	4-Aug-23	e6faadd2aab8409	None	3
Workstation1135	22/06/2023 2:37	4-Aug-23	b0c89945cfa2f4b	None	4
Workstation1135	21/06/2023 6:05	4-Aug-23	8313b9cda1b4b3a	None	5
Workstation1135	2/03/2023 1:09	4-Aug-23	46b83af427e0783	None	6

I have followed the awesome guide on https://data-witches.com/2020/10/21/adding-a-row-rank-based-on-a-different-column-with-power-query/ to add a Rank for each computerDnsName based lastSeen date but I am struggling to understand how to iterate this for each CollectionDate. 

Sample of the Power Query :

    #"Grouped Rows" = Table.Group(#"Changed Type1", {"computerDnsName"}, {{"AllHostGroup", 
    each Table.AddIndexColumn(
        Table.Sort(_,{{"lastSeen", 
        Order.Descending}}), "Row Rank",1,1),
     type table [GUID=nullable text,
     computerDnsName=nullable text,
     lastSeen=nullable datetime, 
     exposureLevel=nullable text,  
     CollectionDate=nullable date,
     Row Rank=nullable number
     ]}
     }),
    #"Expanded AllHostGroup" = Table.ExpandTableColumn(#"Grouped Rows", "AllHostGroup", {"GUID", "computerDnsName", "lastSeen", "exposureLevel", "CollectionDate", "Row Rank"}, {"GUID", "computerDnsName.1", "lastSeen", "exposureLevel", "CollectionDate", "Row Rank"})
in
    #"Expanded AllHostGroup"

 Any advice appericated,

[Anonymous · Accepted solution]

Hi @rayza ,

Please refer to my pbix file to see if it helps you.

Create a custom column.

Table.RowCount(Table.SelectRows(Table.SelectRows(Table.Group(#"Changed Type with Locale", {"computerDnsName", "CollectionDate"}, {{"Data", each _}}),(x)=>x[computerDnsName]=[computerDnsName] and x[CollectionDate]=[CollectionDate]){0}[Data],(y)=>y[lastSeen]>=[lastSeen]))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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