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