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