New column - return column name where <condition>

[Anonymous]

Hello,

I have a challange invented by myself and I am not sure how to approach the situation.

To give you the background - this is going to be used with JIRA.

Challange -> Show issue IDs when the status has been updated within last 24H + show the information about the status (from which one to which one update was made) NOTE: Within last 24H status could be changed few times

Part 1 --> Show issue IDs when the status has been updated within last 24H - that's done. Working very well.

Part 2 --> SHow the information about the status (including note). Here I stuck.

So I already have all necessary data, let's say I have columns (to simplify)

(Date columns are in DateTime type)

ID  |  From To Do  |  To To Do  |  From In Progress  |  To In Progress  |  From DONE  |  To DONE  | LastUpdatedIn24H?|  FromStatus  |  ToStatus

Now:

a) how to create FromStatus coulmn that will contain --> Name of column with the earliest Date/Time within last 24H

(EXAMPLE: let's assume that:

From To Do = 10-JUN-2019 2:00AM

From In Progress = 10-JUN-2019 3:00AM

TODAY()= 11-JUN-2019 1:00AM

-> the result should be "From To Do")

b) equally how to create ToStatus column that will contain --> Name of the column with the latest Date/Time within last 24H

(EXAMPLE: let's assume that:

To In Progress = 10-JUN-2019 2:00AM

To DONE = 10-JUN-2019 3:00AM

TODAY() = 11-JUN-2019 1:00AM

-> the result should be "To DONE"

According to above examples I should be able to create a visualization that will show me:

ID | FromStatus | ToStatus

and add filter where LastUpdatedIn24H? = TRUE

May sounds easy, but the question is how to do it. For me it doesn't seem to be trivial unfortunately.

[Cmcmahan]

This is an interesting problem.

As far as I can tell, with this data, only the most recent changes would appear in this data, so if ticket ABC-123 started the day in ToDo, and had status changed in the last 24 hours in this order :  ToDo -(Time=1)> Done -(T=2)>  ToDo -(T=3)> In Progress -(T=4)> Done. 

The scenario I'm envisioning here is that somebody thinks a ticket is done, so moves it to done, realizes this isn't the case so moves it back to ToDo, and then the process is carried out normally.  Your table would have timestamps that would order like {3, 2, 4, 3, 2, 4}.  Both FromStatus and ToStatus would show Done, which is clearly wrong, since the ticket started the day in ToDo. Even with a normal ticket flow of ToDo->InProg->Done, if it takes multiple days per status, if you're limiting this to only the last 24h, you'll see a ToStatus for the most recent change, but no corresponding FromStatus.  

Other questions I have are: How would a newly created ticket look?  Would it just have a To ToDo event, with no corresponding FromStatus? 

I would re-define your challenge like so:  Show issue IDs that have been updated in the last 24h + Show what the status was 24h ago and what the status is now.

My first instinct would be to unpivot this data, at the source if at all possible.  You end up with a table like this (note that I'm using today's date so that the test file I'm working with will work with a 24h filter):

TicketID	StatusChangeTime	From	To
ABC-123	7/12/2019 2:00AM	ToDo	InProgress
ABC-123	7/12/2019 3:00AM	InProgress	Done
Complex	7/11/2019 12:00PM		ToDo
Complex	7/12/2019 3:00AM	ToDo	Done
Complex	7/12/2019 3:30AM	Done	ToDo
Complex	7/12/2019 4:00AM	ToDo	InProgress
Old	6/10/2019 12:00AM	ToDo	InProgress
Old	6/12/2019 12:00PM	InProgress	Done

This becomes VERY easy to work measures against:

FromStatus = 
VAR earliestToday = CALCULATE(MIN(UnpivotedData[StatusChangeTime]),  UnpivotedData[StatusChangeTime] >= NOW()-1)
RETURN
CALCULATE(SELECTEDVALUE(UnpivotedData[From]), UnpivotedData[StatusChangeTime] = earliestToday)

ToStatus = 
VAR latestToday = CALCULATE(MAX(UnpivotedData[StatusChangeTime]),  UnpivotedData[StatusChangeTime] >= NOW()-1)
RETURN
CALCULATE(SELECTEDVALUE(UnpivotedData[To]), UnpivotedData[StatusChangeTime] = latestToday)

You can filter items that have an event in the last 24h or however you like by changing the measures.

I'm going to post this solution now, and write a second one on how I would either programatically unpivot the original table, or otherwise work with that data directly, assuming you can't get data in an event-style format and only have the original format to work with.

[Cmcmahan · Accepted solution]

So now on to the tricky part. Assuming that the columns you provided is the format of the data we can get from the source, then the same data I presented before would look like this:

ID	From To Do	To To Do	From In Progress	To In Progress	From Done	To Done
ABC-123	7/12/2019 2:00:00 AM	null	7/12/2019 3:00:00 AM	7/12/2019 2:00:00 AM	null	7/12/2019 3:00:00 AM
Complex	7/12/2019 4:00:00 AM	7/12/2019 3:30:00 AM	null	7/12/2019 4:00:00 AM	7/12/2019 3:30:00 AM	7/12/2019 3:00:00 AM
Old	6/10/2019 12:00:00 AM	null	6/12/2019 12:00:00 PM	6/10/2019 12:00:00 AM	null	6/12/2019 12:00:00 PM

We've lost some of the original data because we only have the most recent timestamp from JIRA (which I think is how JIRA offers the info, IIRC), but it's likely what you have to work with @Anonymous so we'll go with it.  

So I spent some time trying to figure out the best way to do this, and the queries when you're using this table are just complex and generally awful.  You're constantly making sure the context is keeping the current ID, and making sure you're only using the right columns.  I never got it working quite right, because I gave up 10 minutes in to just use Power Query to unpivot the tables.  I ended up with this table:

TicketID	Event Timestamp	From	To
ABC-123	7/12/2019 2:00:00 AM	To Do	In Progress
ABC-123	7/12/2019 3:00:00 AM	In Progress	Done
Complex	7/12/2019 3:30:00 AM	Done	To Do
Complex	7/12/2019 4:00:00 AM	To Do	In Progress
Complex	7/12/2019 3:00:00 AM	null	Done
Old	6/10/2019 12:00:00 AM	To Do	In Progress
Old	6/12/2019 12:00:00 PM	In Progress	Done

As you can see, there is less data than my table from the previous post, but it might be all we can get from the source.   Here's the PowerQuery I used to get to this table from the first one:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1jU0MlbSUTLXNzTSNzIwtFQwsjIwcPQFCqGIGsNEiVQYqxOt5JyfW5CTWoEibYJpjrGVMRZz8CnEapt/TgpQykzf0AAiZYjsPjO4DrBwgC8pSmNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"From To Do" = _t, #"To To Do" = _t, #"From In Progress" = _t, #"To In Progress" = _t, #"From Done" = _t, #"To Done" = _t]),
	
    #"From Table" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"From To Do", type datetime}, {"To To Do", type datetime}, {"From In Progress", type datetime}, {"To In Progress", type datetime}, {"From Done", type datetime}, {"To Done", type datetime}}),
    #"Removed To Columns" = Table.RemoveColumns(#"From Table",{"To To Do", "To In Progress", "To Done"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed To Columns", {"ID"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "From Status"}, {"Value", "Event Timestamp"}}),
    #"FromEvents" = Table.TransformColumns(#"Renamed Columns", {{"From Status", each Text.AfterDelimiter(_, " "), type text}}),
	
	
    #"To Table" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"From To Do", type datetime}, {"To To Do", type datetime}, {"From In Progress", type datetime}, {"To In Progress", type datetime}, {"From Done", type datetime}, {"To Done", type datetime}}),
    #"Removed From Columns" = Table.RemoveColumns(#"To Table",{"From To Do", "From In Progress", "From Done"}),
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Removed From Columns", {"ID"}, "Attribute", "Value"),
    #"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Columns2",{{"Attribute", "To Status"}, {"Value", "Event Timestamp"}}),
    #"ToEvents" = Table.TransformColumns(#"Renamed Columns2", {{"To Status", each Text.AfterDelimiter(_, " "), type text}}),
	

    #"Merge Table" = Table.NestedJoin(#"ToEvents", {"ID", "Event Timestamp"}, #"FromEvents", {"ID", "Event Timestamp"}, "FromEvents", JoinKind.FullOuter),
    #"Expanded From Events" = Table.ExpandTableColumn(#"Merge Table", "FromEvents", {"From Status"}, {"From Status"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded From Events",{"ID", "Event Timestamp", "From Status", "To Status"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns",{{"From Status", "From"}, {"To Status", "To"}, {"ID", "TicketID"}})
in
    #"Renamed Columns3"

The trick here was splitting the unpivoting into 2 stages. One for the From events and another for the To events, and then merging those tables back together. 

Because of the potential for blanks in the data due to newer events overwriting old ones, I did have to change the measures from before slightly to account for the possibility of a From not having a matching To event:

ToStatus2 = 
VAR earliestToday = CALCULATE(MIN(UnpivotedSampleData[Event Timestamp]), FILTER(UnpivotedSampleData, UnpivotedSampleData[To]<> BLANK() && UnpivotedSampleData[Event Timestamp] >= NOW()-1))
RETURN
CALCULATE(SELECTEDVALUE(UnpivotedSampleData[To]), UnpivotedSampleData[Event Timestamp] = earliestToday)

And that's how I'd accomplish it with this setup.  That was a cool problem. Thanks for sharing!

[Anonymous]

Hi @Cmcmahan 

Firtst of all - I am so much happy that you took an initiative for checking my problem. Indeed the problem is very interesting for me as well. I started my journey with PowerBI in last week, so we can easily say that I'm newbie here, but doing my best to learn as much as possible and as quick as possible!

Let me answer your questions (hopefully I will not miss any):

[Q] How would a newly created ticket look?  Would it just have a To ToDo event, with no corresponding FromStatus?

[A] Data that I pull to define the From/To Status/Dates is coming from History of the JIRA issue. See the below screenshot how it looks in the UI. As you can see there is no information about anything when item is being created. There is just notification that it has been created. Below you can see how the change when something changes. In my case I am filtering by Field = Status, and after some transformations I get to the point described in my first post.

[Q] Assuming that the columns you provided is the format of the data we can get from the source.

[A] Yes, value that is being returned by the JIRA API is Year,Month,Day,Hour,Minute,Second,Timezone - I just made is simple as seconds and timezone does not matter so much.

[Q] we only have the most recent timestamp from JIRA (which I think is how JIRA offers the info...

Basically you can pull whole history with every entry, but I use List.Max to get the latest dates per status change.

So if we have a situation like

Today 1-> From To Do - to In Progress

Today 2-> from In Progress - to To Do

Today 3 -> from To Do to - In Progress

I am working on date within Today 3 (the status transition for Today 1 and Today 3 is the same).

Now I am going to read your posts 100 times again and experiment with my dataset. Thank you very much for your help! Once I will complete the task on my end, I will mark your post as a Solution.

Thanks again and wish me luck!!! 🙂  

UPDATE: It's almost 1AM and I spent a really lot time today on this, but it's working!!! @Cmcmahan - thank you soooo much! Here is the proof 🙂 A bit edited, but the production data is here 🙂