Grouped By Query

[Bernie6808]

I have a table of Academics working on projects within specific departments which I need to summarise by Subproject Data ID. Each project has a single 'Lead Subproject' and each Academic has a Credit %. Project and Subproject are concatenated to provide Subproject Data ID. The table is sorted Z-A on Lead_Subproject and Largest to Smallest on Credit.

I have used 'Group by' to consolidate the data by Subproject_Data_ID, Project and Subproject, but I need to populate Lead_Subproject and Project_Lead with the first matching entries in the first table (which is why it is sorted): 

At the moment I have to do this in Excel after loading the query using Xlookup/Index(Match()), but is there a way to do it within the Group By code? 

Best,

Bernie

[Manoj_Nair]

@Bernie6808 - not sure if you manage to achieve the desired output, but like to spin with an different way of approach. Try this and let me know if this fixes your issues. Give me a shout out if you need any more details PBIX File attached. Grouped by Query 

[ImkeF · Accepted solution]

Hi @Bernie6808 ,
just apply a dummy-operation (like SUM or MAX) on those 2 columns and afterwards tweak the generated M-code a bit:
List.Sum or List.Max should become List.First instead.
Regarding the sorting, you might want to consider a Table.Buffer: 

https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-i...

[Bernie6808]

Perfect! Thanks!