Forum Discussion
Creating a column using different clauses and like statements
I went ahead and did this all in Power Query, so I attached the pbix file below so you walk through each step and see what I did. Too much to explain all here as it's much easier to just see it, but let me know if you have any questions. One thing, I did split EntryDate column into two columns, one for date and one for time. The way compression works you generally dont want those values combined.
Final table
Here's the file
can you load some sample data and what the goal output would be?
Hi Nick,
Here is some sample data from the HISTORY_ENTRY table.
JOB_TICKET_ID ENTRY_DATE ENTRY_TEXT 227925 5/30/2019 9:40 Status changed from Open to Closed 227925 5/30/2019 9:40 Added new tech note. 227925 5/30/2019 9:31 Reassigned-Ticket E-Mail sent to Joe Smith 227925 5/30/2019 8:03 Request Type changed from Business Systems 227925 5/30/2019 8:03 Status changed from Open to In Progress 227925 5/29/2019 18:19 Created by Molly Davis 227925 5/29/2019 18:19 Assigned to Joe Smith 227922 5/27/2019 8:03 Status changed from Open to Closed 227922 5/22/2019 8:03 Status changed from Open to Resolved 227922 5/21/2019 18:19 Created by Molly Davis 227922 5/21/2019 18:19 Assigned to Joe Smith
So the idea here would be getting a new field called something like "First_Resolution_Date"This would be the date the first time you see either "To Resolved" or "To Closed" in the history entry notes.
In this example:
the First_Resolution_Date for Job_Ticket_ID 227925 would be 5/30/2019 9:40:14 AM
the First_Resolution_Date for Job_Ticket_ID 227922 would be 5/22/2019 8:03:25 AM (note this one has both a "To Resolved" and a "To Closed", but the "To Resolved" is earlier.
Here is how this table should look:
JOB_TICKET_ID ENTRY_DATE First_Resolution_Date ENTRY_TEXT 227925 5/30/2019 9:40 5/30/2019 9:40:14 AM Status changed from Open to Closed 227925 5/30/2019 9:40 5/30/2019 9:40:14 AM Added new tech note. 227925 5/30/2019 9:31 5/30/2019 9:40:14 AM Reassigned-Ticket E-Mail sent to Joe Smith 227925 5/30/2019 8:03 5/30/2019 9:40:14 AM Request Type changed from Business Systems 227925 5/30/2019 8:03 5/30/2019 9:40:14 AM Status changed from Open to In Progress 227925 5/29/2019 18:19 5/30/2019 9:40:14 AM Created by Molly Davis 227925 5/29/2019 18:19 5/30/2019 9:40:14 AM Assigned to Joe Smith 227922 5/27/2019 8:03 5/22/2019 8:03:25 AM Status changed from Open to Closed 227922 5/22/2019 8:03 5/22/2019 8:03:25 AM Status changed from Open to Resolved 227922 5/21/2019 18:19 5/22/2019 8:03:25 AM Created by Molly Davis 227922 5/21/2019 18:19 5/22/2019 8:03:25 AM Assigned to Joe Smith There is also a master table called "JOB_TICKET" that is linked on the JOB_TICKET_ID and that is why in the orginal query we had:
where he.JOB_TICKET_ID = jt.JOB_TICKET_ID
Maybe it isn't needed in Power BI.
I hope this helps.
Thanks again!
I went ahead and did this all in Power Query, so I attached the pbix file below so you walk through each step and see what I did. Too much to explain all here as it's much easier to just see it, but let me know if you have any questions. One thing, I did split EntryDate column into two columns, one for date and one for time. The way compression works you generally dont want those values combined.
Final table
Here's the file
