Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I need to return 3 records for each person in a table. I really don't care if it's Top 3 or Last 3 or random. Each ID number (person) can have 1 to unlimited records (probably more like 15).
Basic setup is:
ResourceID | OrderID | Date | Other info/fields |
ABC-1 | 12301 | 1/1/24 |
|
ABC-1 | 12302 | 1/2/24 |
|
ABC-2 | 12303 | 1/3/24 |
|
ABC-3 | 12304 | 1/4/24 |
|
ABC-3 | 12305 | 1/5/24 |
|
ABC-3 | 12306 | 1/6/24 |
|
ABC-3 | 12307 | 1/7/24 |
|
So I want 3 OrderIDs for each ResourceID, or 2 or 1. I tried grouping.. I'm stuck. Help!
Solved! Go to Solution.
Table.Group and then take the first three rows using a custom aggregation
let
Source = Table.FromColumns({
{"ABC-1","ABC-1","ABC-2","ABC-3","ABC-3","ABC-3","ABC-3"},
List.Numbers(12301,7),
List.Dates(#date(2024,1,1),7,#duration(1,0,0,0))},
type table[ResourceID=text, OrderID=Int64.Type, Date=date]),
#"Grouped Rows" = Table.Group(Source, {"ResourceID"}, {
{"3 Rows", each Table.FirstN(_,3), type table [ResourceID=text, OrderID=number, Date=date]}}),
#"Expanded 3 Rows" = Table.ExpandTableColumn(#"Grouped Rows", "3 Rows", {"OrderID", "Date"})
in
#"Expanded 3 Rows"
Results
Table.Group and then take the first three rows using a custom aggregation
let
Source = Table.FromColumns({
{"ABC-1","ABC-1","ABC-2","ABC-3","ABC-3","ABC-3","ABC-3"},
List.Numbers(12301,7),
List.Dates(#date(2024,1,1),7,#duration(1,0,0,0))},
type table[ResourceID=text, OrderID=Int64.Type, Date=date]),
#"Grouped Rows" = Table.Group(Source, {"ResourceID"}, {
{"3 Rows", each Table.FirstN(_,3), type table [ResourceID=text, OrderID=number, Date=date]}}),
#"Expanded 3 Rows" = Table.ExpandTableColumn(#"Grouped Rows", "3 Rows", {"OrderID", "Date"})
in
#"Expanded 3 Rows"
Results
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.