Filter - Last Submission Date

[EbyEaso]

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.

[Greg_Deckler]

@EbyEaso 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...

[EbyEaso]

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]

@EbyEaso That's just an Enter Data query where I pasted your data into.

[EbyEaso]

@Greg_Deckler SO what I need to do. Can you please help me out.

[Greg_Deckler]

@EbyEaso Go into Advanced Editor. Take everything underneath your Source line and possibly Navigation line and replace it with:

    #"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"

In the #"Changed Type" line if you left in a Navigation line then you will need to change Source to Navigation. 

It's hard for me to tell you exactly because I have no idea what transformations you are performing in your query.

[EbyEaso]

Hello @Greg_Deckler 

This is what I have in my Advanced Editor. Actually my source is from Sharepoint List. So what I need to do in here.

Please note: I have changed the web address below to share with you. I dont want to put out my actual sharepoint address here.

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]
in
#"0eba12ed-d2f6-4bed-a89a-84d46f80e14a"

[Greg_Deckler · Accepted solution]

@EbyEaso 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"

[EbyEaso]

Hello @Greg_Deckler ,

Great Awesome. I think its working. You are super. Appreciate your support and guidance. Good.

So later if i have additional columns just add it in here rite.

  #"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}})

[Greg_Deckler]

@EbyEaso As in additional columns you want to group by?

[EbyEaso]

@Greg_Deckler  Like later in future if i have additional column to add in the share point online list.

[Greg_Deckler]

@EbyEaso You would have to add an aggregation for them in your Group by. Just click the gear icon next to that step in Applied steps.

[EbyEaso]

@Greg_Deckler Alright. Noted. Thank Yoy much. Appreciate it.