Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-2074680%22%20slang%3D%22en-US%22%3EPower%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2074680%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%20%26nbsp%3BI%20have%20a%20problem%20in%20Power%20Query%20bringing%20back%20the%20corresponding%20cells%20for%20a%20new%20column%20of%20data.%26nbsp%3B%20%26nbsp%3BThere%20are%204%20columns%20-%20Person%20Name%2C%20Certification%20Code%2C%20Date%20Issued%20and%20Course%20Code.%26nbsp%3B%20%26nbsp%3BI%20am%20looking%20for%20the%20most%20recent%20date%20the%20certification%20was%20issued%20with%20the%20corresponding%20course%20code.%26nbsp%3B%20%26nbsp%3BIf%20I%20take%20my%20source%20data%20into%20Power%20Query%20and%20use%20the%26nbsp%3B%20group%20By%20with%20Person%20Name%20and%20Certification%20Code%20and%20add%20a%20new%20column%20called%20Max%20certification%20Date%20with%20the%20Max%20on%20Date%20Issued%2C%20this%20brings%20back%20the%20correct%20data%20and%20all%20I%20now%20need%20is%20the%20corresponding%20course%20code%20for%20that%20date%20and%20certification%20code.%26nbsp%3B%20%26nbsp%3B%20I%20thought%20I%20had%20to%20add%20a%20further%20column%20with%20the%20all%20rows%20option%20and%20then%20select%20the%20additional%20code%20but%20when%20i%20do%20this%20it%20duplicates%20the%20certification%20codes.%26nbsp%3B%20Any%20help%20would%20be%20much%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2074680%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2074868%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2074868%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F934639%22%20target%3D%22_blank%22%3E%40John_B1967%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%2C%20with%20Group%20By%20select%20all%20rows%20(no%20aggregations)%20and%20change%20...each%20_%2C%20type%20table...%20in%20formula%20bar%20on%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%23%22Grouped%20Rows%22%20%3D%20Table.Group(%0A%20%20PrevStep%2C%0A%20%20%7B%22Person%20Name%22%2C%20%22Certification%20Code%22%7D%2C%0A%20%20%7B%7B%22Custom%22%2C%20each%20Table.FirstN(Table.Sort(_%2C%20%7B%7B%22Date%22%2C%20Order.Descending%7D%7D)%2C1)%7D%7D)%2C%0A%2F%2F%20and%20expand%20it%20with%20next%20step%0A%23%22Expanded%20Custom%22%20%3D%20Table.ExpandTableColumn(%0A%20%20%20%23%22Grouped%20Rows%22%2C%0A%20%20%20%22Custom%22%2C%20%7B%22Date%22%2C%20%22Course%20Code%22%7D%2C%20%7B%22Date%22%2C%20%22Course%20Code%22%7D)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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