Power Query

Copper Contributor

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. 

5 Replies

@John_B1967 

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"})

 

@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]}})

@John_B1967 

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.

@Sergei Baklan Fantastic, that has worked perfectly.  Thank you so much for your help.  

@John_B1967 , you are welcome, glad to help