cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EbyEaso
Helper I
Helper I

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 IDSubmit Date
1A21-Sep-21
1B21-Sep-21
1C21-Sep-21
1A20-Jul-21
1B20-Jul-21
1C20-Jul-21
1A20-Jun-21
1C20-Jun-21
1D20-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 IDSubmit Date
1A21-Sep-21
1B21-Sep-21
1C21-Sep-21
1D20-May-21

 

Can anyone help me with this please.

 

Thank You.

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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

12 REPLIES 12
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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)

  

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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"

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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

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

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.