Forum Discussion
Filter - Last Submission Date
Hello,
I have a query comes from Excel. I need to filter only the data whcih has the last submission date.
Below is the table for example. 1A, 1B & 1C has submisison in Sep & Jul. 1A & 1C has submission in Sep, Jul & Jun. Finally 1D has only submission in May.
| Student ID | Submit Date |
| 1A | 21-Sep-21 |
| 1B | 21-Sep-21 |
| 1C | 21-Sep-21 |
| 1A | 20-Jul-21 |
| 1B | 20-Jul-21 |
| 1C | 20-Jul-21 |
| 1A | 20-Jun-21 |
| 1C | 20-Jun-21 |
| 1D | 20-May-21 |
So I need to filter out this in Power Query with the last submission for each student ID.
Whcih I need it like this.
| Student ID | Submit Date |
| 1A | 21-Sep-21 |
| 1B | 21-Sep-21 |
| 1C | 21-Sep-21 |
| 1D | 20-May-21 |
Can anyone help me with this please.
Thank You.
Anonymous OK, the try:
let Source = SharePoint.Tables("https://myselfsharepoint.com/sites/programs-myself/", [Implementation=null, ApiVersion=15]), #"0eba12ed-d2f6-4bed-a89a-84d46f80e14a" = Source{[Id="0eba12ed-d2f6-4bed-a89a-84d46f80e14a"]}[Items], #"Changed Type" = Table.TransformColumnTypes(#"0eba12ed-d2f6-4bed-a89a-84d46f80e14a",{{"Student ID", type text}, {"Submit Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Student ID"}, {{"Submit Date", each List.Max([Submit Date]), type nullable date}}) in #"Grouped Rows"
12 Replies
- Greg_Deckler
Community Champion
Anonymous Try:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnRU0lEyMtQNTi3QNTJUitUBCjlhCjljCoE1Guh6leagakQVcsYUgmvMw1SFEHKBCPkmVoKFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Submit Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", type text}, {"Submit Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Student ID"}, {{"Submit Date", each List.Max([Submit Date]), type nullable date}}) in #"Grouped Rows"It's a basic Group by...
Thank You. Greg_Deckler
What is this. Am actually newer to Power BI. This i can use in Power Query Editor?
(Json.Document(Binary.Decompress(Binary.FromText("i45WMnRU0lEyMtQNTi3QNTJUitUBCjlhCjljCoE1Guh6leagakQVcsYUgmvMw1SFEHKBCPkmVoKFYgE=", BinaryEncoding.Base64)- Greg_Deckler
Anonymous That's just an Enter Data query where I pasted your data into.