Forum Discussion

KG1's avatar
KG1
Resolver I
4 years ago
Solved

Last record based on latest date - Power Query

Hi  I need to find the latest entry using the lastest date against each record. I need to do this in Power Query   My table looks like this     I have used the group by method to find the...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi KG1 ,

    I have created a sample pbix file, you can find the attachment for the details.  You can update the codes in your Advanced Editor as below:

    1. Expanded table also include the column "Date created"

    2. Filter the rows which the value of the column [Date created] is equal to max([Date created])

    #"Grouped Rows" = Table.Group(#"Filtered Rows4", {"Person Number"}, {{"Count", each List.Max([Date created]), type nullable datetime}, {"Table", each _, type table [Date created=nullable datetime, Person Number=nullable text, Ethnicity=nullable text]}}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Table", {"Date created", "Ethnicity"}, {"Date created", "Ethnicity"}),
    #"Filter Rows" = Table.SelectRows( #"Expanded Table", each([Count]=[Date created]))
    in
    #"Filter Rows"

    In addition, you can refer the following links to get it.

    load latest date record

    How to filter a table to show only most recent date by group in Power Query

    Best Regards

puraskar_award's avatar
puraskar_award
Frequent Visitor
1 year ago
  • Video https://www.youtube.com/watch?v=hidJ5T_DYQ0was great. Summary -
  • Simply take the groupby for the entire data by the unique ID column -- on which we need to find the latest record.
  • Ensure the to aggregate for Date is Max of that Date column and ADD another metric as "ALL" to later expand all the rows in the table.
  • Expand the table in column ALL
  • After that filter the column for date filter where it equals to the Max Date column
  • Remove columns that are no longer needed i.e Max Date and Count of Row