Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 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)
@EbyEaso That's just an Enter Data query where I pasted your data into.
@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.
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"
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?
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
105 | |
104 | |
89 | |
65 |