Forum Discussion
Solved
Latest entry with Conditions
Hello, I have been struggling with this for a bit now. I am looking for a why to look at some audit information to find out the last time a record was submitted. I want to group the data on the TI...
Here is the query. Note that the date for the first item disagrees.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5Zdbb6JQFIX/yo7PXjiHO2+1aTKZh2kzdh6atg8ou8KIYOBY47+fDYiIgxAsxsQmTSSHsl3fOmsBvr72mKZwWea9fk9XdZU+/sQY3e0+oXTAtcHPdTDgyT9zOvSzQ33ERlziHDizFCO/IEYfZwIdeMEYvAAijFdhECOIEGZh8OFFS1t4YQBLjGN7jn146423MPO92cIL5vDyMOnDNlyDHWF+QbIuXFuk6679iTT108MNfYtwvRiEt0SIXURBJ1ZhJMAOnOwKj/5imhNFpCrRIFyEKcYCwo9kXASLINz46Mxx+NYjiAc7FhgFMBE0w44ceKbhKa2a0TLVUljvvZ9bKHdp4WQ9XXqpzrvVKgo/6fIR/EDboQNSJNYx3Lt2MCfyETz6Tr44gMcVBrT2CzfF2hMGTuJdNsv2W+MpsmY04Y3r8PKhisWTCc8eXcBoC2PaqL9pTix48tGmfKxXjp3kJj+R7SoGIvIoSeQIbW0SLdiE0QKcMMAhPLtIKUmSEiN5Zfvg0pamuUvPpBGYoh+SCbRGIaN5NHi2QBEPYYIIQShoPOUR7Zlb8eXbYWvXVPbFUJSGfstekYVKlxae36vfeSKOuvUQRSEl7T5zAJ3WiIbO2Ze6pe+GciuN20G37IzxHE3NT4R2mihAkzRAs529H1G4/M+8g41xYLqFjKWtfJUr2e2Kbly1qZELyeahemN/M1bzATfcuxJt4aDZoYNXfJxV0cmyJDfRjWvoGNvHO72rtK1ctSamdaupunLH5p1RuQr5GlfT9xZdT509HRgmFaKZVgLYNZlzS9Vvv3Vl3MJFs1sXr9i8SkLN0NQmwnE9YRE/RT+jfCdkmVLXsi7WvyoCXdXkrIBcq4+OXqjm8iEBl3d3EN3au3DDBSzjFi6a3bp4xQJWElJMpCbCcT2hun/SpCttC3hCltlofFtZFytgFYGhm2r6Hq9rDT9VlEK1LJUIzF2vZYt/hwKWcAsXebcuXrOAVYSGzBpzMq4hNEbFm550zg++E6q41LGqy9WvAsDUZSN9fzaYVBscmRWitcHdep7rV/fP73Thpst3RFtYqHdp4dWaV41ncMNowhvX4Rn7hDOlde9OaTKVbjVdqHXH8t//AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time_Audit_RecID = _t, Time_Sheet_RecID = _t, Member_Identifier = _t, Member_Name = _t, Submitted_By = _t, Date_Start = _t, Date_End = _t, Date_Audit_UTC = _t, Audit_Text = _t, Time_Zone = _t, #"Date _Submitted" = _t]), #"Grouped Rows" = Table.Group(Source, {"Time_Sheet_RecID"}, {{"Rows", each _, type table [Time_Audit_RecID=nullable text, Time_Sheet_RecID=nullable text, Member_Identifier=nullable text, Member_Name=nullable text, Submitted_By=nullable text, Date_Start=nullable text, Date_End=nullable text, Date_Audit_UTC=nullable text, Audit_Text=nullable text, Time_Zone=nullable text, Date _Submitted=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Sort(Table.SelectRows([Rows], each Text.StartsWith([Audit_Text], "Submit to Approver")),{{"Date_Audit_UTC", Order.Ascending}})[Date_Audit_UTC]{0}), #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Time_Audit_RecID", "Member_Identifier", "Member_Name", "Submitted_By", "Date_Start", "Date_End", "Date_Audit_UTC", "Audit_Text", "Time_Zone", "Date _Submitted"}, {"Time_Audit_RecID", "Member_Identifier", "Member_Name", "Submitted_By", "Date_Start", "Date_End", "Date_Audit_UTC", "Audit_Text", "Time_Zone", "Date _Submitted"}) in #"Expanded Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
lbendlin
Super User
Super UserPlease provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Sorry for not including this before. Below is some sample data that I am working with. The far right column is an example of what I would like to see as the results. There has been a slight change of what I need to get. I need to get the first time a Time sheet (Time_Sheet_RecID) has an aduit text starting with "Submit to Approver". Then I need to grab the time from the Date_Audit_UTC and supply that as the result.
| Time_Audit_RecID | Time_Sheet_RecID | Member_Identifier | Member_Name | Submitted_By | Date_Start | Date_End | Date_Audit_UTC | Audit_Text | Time_Zone | Date _Submitted |
| 1642332 | 7575 | UserA | User A | User A | 26-Jun-22 | 2-Jul-22 | 7/1/2022 21:48 | User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge." | Eastern Standard Time | 7/5/2022 15:41 |
| 1642333 | 7575 | UserA | User A | User A | 26-Jun-22 | 2-Jul-22 | 7/1/2022 21:48 | Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval | Eastern Standard Time | 7/5/2022 15:41 |
| 1644368 | 7575 | UserA | User A | User B | 26-Jun-22 | 2-Jul-22 | 7/5/2022 14:25 | Tier 1 has rejected: Please updated rejected time entries to replect work done. There are several hours in here that belong in client tickets. See notes on each rejected time entry. | Eastern Standard Time | 7/5/2022 15:41 |
| 1645133 | 7575 | UserA | User A | User A | 26-Jun-22 | 2-Jul-22 | 7/5/2022 15:41 | User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge." | Eastern Standard Time | 7/5/2022 15:41 |
| 1645134 | 7575 | UserA | User A | User A | 26-Jun-22 | 2-Jul-22 | 7/5/2022 15:41 | Submit to Approver / Header Status Changed / Old Status - Rejected / New Status - Errors Corrected | Eastern Standard Time | 7/5/2022 15:41 |
| 1648721 | 7575 | UserA | User A | User B | 26-Jun-22 | 2-Jul-22 | 7/7/2022 12:33 | Tier 1 has approved | Eastern Standard Time | 7/5/2022 15:41 |
| 1648732 | 7575 | UserA | User A | User B | 26-Jun-22 | 2-Jul-22 | 7/7/2022 12:33 | Time Sheet changed from Errors Corrected to Approved by Tier 1 | Eastern Standard Time | 7/5/2022 15:41 |
| 1652468 | 7644 | UserA | User A | User A | 3-Jul-22 | 9-Jul-22 | 7/8/2022 21:54 | User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge." | Eastern Standard Time | 7/8/2022 21:54 |
| 1652469 | 7644 | UserA | User A | User A | 3-Jul-22 | 9-Jul-22 | 7/8/2022 21:54 | Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval | Eastern Standard Time | 7/8/2022 21:54 |
| 1653303 | 7644 | UserA | User A | User B | 3-Jul-22 | 9-Jul-22 | 7/11/2022 12:41 | Tier 1 has approved | Eastern Standard Time | 7/8/2022 21:54 |
| 1653316 | 7644 | UserA | User A | User B | 3-Jul-22 | 9-Jul-22 | 7/11/2022 12:41 | Time Sheet changed from Pending Approval to Approved by Tier 1 | Eastern Standard Time | 7/8/2022 21:54 |
| 1662548 | 7703 | UserA | User A | User A | 10-Jul-22 | 16-Jul-22 | 7/15/2022 22:57 | User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge." | Eastern Standard Time | 7/15/2022 22:57 |
| 1662549 | 7703 | UserA | User A | User A | 10-Jul-22 | 16-Jul-22 | 7/15/2022 22:57 | Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval | Eastern Standard Time | 7/15/2022 22:57 |
| 1666865 | 7703 | UserA | User A | User B | 10-Jul-22 | 16-Jul-22 | 7/18/2022 21:47 | Tier 1 has approved | Eastern Standard Time | 7/15/2022 22:57 |
| 1666890 | 7703 | UserA | User A | User B | 10-Jul-22 | 16-Jul-22 | 7/18/2022 21:47 | Time Sheet changed from Pending Approval to Approved by Tier 1 | Eastern Standard Time | 7/15/2022 22:57 |
| 1675638 | 7726 | UserA | User A | User A | 17-Jul-22 | 23-Jul-22 | 7/23/2022 17:03 | User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge." | Eastern Standard Time | 7/23/2022 17:03 |
| 1675639 | 7726 | UserA | User A | User A | 17-Jul-22 | 23-Jul-22 | 7/23/2022 17:03 | Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval | Eastern Standard Time | 7/23/2022 17:03 |
| 1676380 | 7726 | UserA | User A | User B | 17-Jul-22 | 23-Jul-22 | 7/25/2022 12:22 | Tier 1 has approved | Eastern Standard Time | 7/23/2022 17:03 |
| 1676399 | 7726 | UserA | User A | User B | 17-Jul-22 | 23-Jul-22 | 7/25/2022 12:22 | Time Sheet changed from Pending Approval to Approved by Tier 1 | Eastern Standard Time | 7/23/2022 17:03 |
| 1687951 | 7765 | UserA | User A | User A | 24-Jul-22 | 30-Jul-22 | 7/29/2022 23:23 | User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge." | Eastern Standard Time | 7/29/2022 23:23 |
| 1687952 | 7765 | UserA | User A | User A | 24-Jul-22 | 30-Jul-22 | 7/29/2022 23:23 | Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval | Eastern Standard Time | 7/29/2022 23:23 |
| 1688311 | 7765 | UserA | User A | User B | 24-Jul-22 | 30-Jul-22 | 8/1/2022 12:03 | Tier 1 has approved | Eastern Standard Time | 7/29/2022 23:23 |
| 1688320 | 7765 | UserA | User A | User B | 24-Jul-22 | 30-Jul-22 | 8/1/2022 12:03 | Time Sheet changed from Pending Approval to Approved by Tier 1 | Eastern Standard Time | 7/29/2022 23:23 |
| 1697386 | 7810 | UserA | User A | User A | 31-Jul-22 | 6-Aug-22 | 8/5/2022 21:22 | User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge." | Eastern Standard Time | 8/5/2022 21:22 |
| 1697387 | 7810 | UserA | User A | User A | 31-Jul-22 | 6-Aug-22 | 8/5/2022 21:22 | Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval | Eastern Standard Time | 8/5/2022 21:22 |
| 1698288 | 7810 | UserA | User A | User B | 31-Jul-22 | 6-Aug-22 | 8/8/2022 12:14 | Tier 1 has approved | Eastern Standard Time | 8/5/2022 21:22 |
| 1698294 | 7810 | UserA | User A | User B | 31-Jul-22 | 6-Aug-22 | 8/8/2022 12:14 | Time Sheet changed from Pending Approval to Approved by Tier 1 | Eastern Standard Time | 8/5/2022 21:22 |
- lbendlin
Here is the query. Note that the date for the first item disagrees.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5Zdbb6JQFIX/yo7PXjiHO2+1aTKZh2kzdh6atg8ou8KIYOBY47+fDYiIgxAsxsQmTSSHsl3fOmsBvr72mKZwWea9fk9XdZU+/sQY3e0+oXTAtcHPdTDgyT9zOvSzQ33ERlziHDizFCO/IEYfZwIdeMEYvAAijFdhECOIEGZh8OFFS1t4YQBLjGN7jn146423MPO92cIL5vDyMOnDNlyDHWF+QbIuXFuk6679iTT108MNfYtwvRiEt0SIXURBJ1ZhJMAOnOwKj/5imhNFpCrRIFyEKcYCwo9kXASLINz46Mxx+NYjiAc7FhgFMBE0w44ceKbhKa2a0TLVUljvvZ9bKHdp4WQ9XXqpzrvVKgo/6fIR/EDboQNSJNYx3Lt2MCfyETz6Tr44gMcVBrT2CzfF2hMGTuJdNsv2W+MpsmY04Y3r8PKhisWTCc8eXcBoC2PaqL9pTix48tGmfKxXjp3kJj+R7SoGIvIoSeQIbW0SLdiE0QKcMMAhPLtIKUmSEiN5Zfvg0pamuUvPpBGYoh+SCbRGIaN5NHi2QBEPYYIIQShoPOUR7Zlb8eXbYWvXVPbFUJSGfstekYVKlxae36vfeSKOuvUQRSEl7T5zAJ3WiIbO2Ze6pe+GciuN20G37IzxHE3NT4R2mihAkzRAs529H1G4/M+8g41xYLqFjKWtfJUr2e2Kbly1qZELyeahemN/M1bzATfcuxJt4aDZoYNXfJxV0cmyJDfRjWvoGNvHO72rtK1ctSamdaupunLH5p1RuQr5GlfT9xZdT509HRgmFaKZVgLYNZlzS9Vvv3Vl3MJFs1sXr9i8SkLN0NQmwnE9YRE/RT+jfCdkmVLXsi7WvyoCXdXkrIBcq4+OXqjm8iEBl3d3EN3au3DDBSzjFi6a3bp4xQJWElJMpCbCcT2hun/SpCttC3hCltlofFtZFytgFYGhm2r6Hq9rDT9VlEK1LJUIzF2vZYt/hwKWcAsXebcuXrOAVYSGzBpzMq4hNEbFm550zg++E6q41LGqy9WvAsDUZSN9fzaYVBscmRWitcHdep7rV/fP73Thpst3RFtYqHdp4dWaV41ncMNowhvX4Rn7hDOlde9OaTKVbjVdqHXH8t//AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time_Audit_RecID = _t, Time_Sheet_RecID = _t, Member_Identifier = _t, Member_Name = _t, Submitted_By = _t, Date_Start = _t, Date_End = _t, Date_Audit_UTC = _t, Audit_Text = _t, Time_Zone = _t, #"Date _Submitted" = _t]), #"Grouped Rows" = Table.Group(Source, {"Time_Sheet_RecID"}, {{"Rows", each _, type table [Time_Audit_RecID=nullable text, Time_Sheet_RecID=nullable text, Member_Identifier=nullable text, Member_Name=nullable text, Submitted_By=nullable text, Date_Start=nullable text, Date_End=nullable text, Date_Audit_UTC=nullable text, Audit_Text=nullable text, Time_Zone=nullable text, Date _Submitted=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Sort(Table.SelectRows([Rows], each Text.StartsWith([Audit_Text], "Submit to Approver")),{{"Date_Audit_UTC", Order.Ascending}})[Date_Audit_UTC]{0}), #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Time_Audit_RecID", "Member_Identifier", "Member_Name", "Submitted_By", "Date_Start", "Date_End", "Date_Audit_UTC", "Audit_Text", "Time_Zone", "Date _Submitted"}, {"Time_Audit_RecID", "Member_Identifier", "Member_Name", "Submitted_By", "Date_Start", "Date_End", "Date_Audit_UTC", "Audit_Text", "Time_Zone", "Date _Submitted"}) in #"Expanded Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".