Forum Discussion

John_B1967's avatar
John_B1967
Copper Contributor
Jan 19, 2021

Power Query

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. 

power bi

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 19, 2021

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

     

    • John_B1967's avatar
      John_B1967
      Copper Contributor
      Jan 19, 2021

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

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 19, 2021

        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.

Resources