Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 lastest date by Person Number
but when I expand the table it brings back all records
#"Grouped Rows" = Table.Group(#"Filtered Rows4", {"Person Number"}, {{"Count", each List.Max([Date created]), type datetime}, {"Table", each _, type table [Date created=datetime, Person Number=text, Ethnicity=text]}}),
#"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Table", {"Ethnicity"}, {"Ethnicity"})
in
#"Expanded Table"
What do I need to do to only have the latest record?
I need it to look like this
Thank you in advance
Solved! Go to Solution.
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
@KG1 , refer if this blog can help
https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/
User | Count |
---|---|
83 | |
77 | |
71 | |
69 | |
54 |
User | Count |
---|---|
105 | |
100 | |
91 | |
79 | |
68 |