Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
Solved! Go to 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.
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.
User | Count |
---|---|
93 | |
83 | |
77 | |
72 | |
66 |
User | Count |
---|---|
115 | |
103 | |
93 | |
64 | |
61 |