Jan 19 2021 07:48 AM
Hi, I have a problem in Power Query bringing back the corresponding cells for a new column of data. There are 4 columns - Person Name, Certification Code, Date Issued and Course Code. I am looking for the most recent date the certification was issued with the corresponding course code. If I take my source data into Power Query and use the group By with Person Name and Certification Code and add a new column called Max certification Date with the Max on Date Issued, this brings back the correct data and all I now need is the corresponding course code for that date and certification code. I thought I had to add a further column with the all rows option and then select the additional code but when i do this it duplicates the certification codes. Any help would be much appreciated.
Jan 19 2021 08:41 AM
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"})
Jan 19 2021 09:14 AM
@Sergei Baklan 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]}})
Jan 19 2021 09:27 AM
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.
Jan 19 2021 09:40 AM
@Sergei Baklan Fantastic, that has worked perfectly. Thank you so much for your help.