Forum Discussion
Last record based on latest date - Power Query
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.
How to filter a table to show only most recent date by group in Power Query
Best Regards
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.
How to filter a table to show only most recent date by group in Power Query
Best Regards
Bah! Obvious, when you think about it! Thank you for saving me from my own idiocy 🙂