Forum Discussion
Power Query
As variant, with Group By select all rows (no aggregations) and change ...each _, type table... in formula bar on
#"Grouped Rows" = Table.Group(
PrevStep,
{"Person Name", "Certification Code"},
{{"Custom", each Table.FirstN(Table.Sort(_, {{"Date", Order.Descending}}),1)}}),
// and expand it with next step
#"Expanded Custom" = Table.ExpandTableColumn(
#"Grouped Rows",
"Custom", {"Date", "Course Code"}, {"Date", "Course Code"})
SergeiBaklan Thanks for your quick response. Would you mind helping me with your reply as i am not a programmer and quite new to Power Query. The code is currently as follows where I have selected all rows with no aggregation. Can you explain what I need to do next please. Thanks very much.
= Table.Group(Source, {"username", "certification_code"}, {{"all rows", each _, type table [username=text, certification_code=number, certification_issue_date=datetime, always_valid=number, certification_expire_date=datetime, code=number, type=text]}})
Having this your code
= Table.Group(Source, {"username", "certification_code"}, {{"all rows", each _, type table [username=text, certification_code=number, certification_issue_date=datetime, always_valid=number, certification_expire_date=datetime, code=number, type=text]}})first in formula bar remove type table.. records as
= Table.Group(Source, {"username", "certification_code"}, {{"all rows", each _ }})now replace in formula bar each _ on each Table.FirstN(Table.Sort(_, {{"certification_issue_date", Order.Descending}}),1), that will be as
= Table.Group(Source, {"username", "certification_code"}, {{"all rows", each Table.FirstN(Table.Sort(_, {{"certification_issue_date", Order.Descending}}),1) }})On next step expand column with resulting tables (it shall be only one row in each) selecting desired fields.
SergeiBaklan Fantastic, that has worked perfectly. Thank you so much for your help.
