Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EbyEaso
Helper III
Helper III

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.