Need help with parent child hierarchy / decomposition tree

[Anonymous]

Hi!

I have some serious troble visualizing my data. I have tried for days now, but i cannot wrap my mind around it.

Would love to get some support on this, have a nice day!

My data looks like this (this is test-data ofcourse):

Basically i want to follow each case in a sankey chart or decomposition tree like this (also if possible, visualize all cases that follow the same assignee path in the same flow):

Im guessing that i need to add steps to my data based on when the timestamp, like this:

I have worked some with PATH before, like organisational structure with employee and manager. But here i am completely lost.

[lbendlin]

Sankey sounds like a possible option, but the first step would be to add From/To columns to your source data, for example in Power Query.

Please provide sample data in usable format (not as a picture - maybe insert into a table?) .

[Anonymous]

Hello! Thanks for replying, here is some example data. I agree with you on the FROM/TO part. CurrentAssignee (solver) is the TO part. And the first previous assignee (based on date) is the FROM part. The previous assignee between that is probably the "multi-level" i guess.

My table is called CaseHistory

I've numbered each assignee on case ID 141054, just to make things clear which assignee is first etc...

I'm getting HTML errors when pasting the table here, so here's a link to a google drive:

Sankey - Google Drive

[Anonymous]

Hi @Anonymous ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Add index column in Power Query Editor group by CaseID and sort by Timestamp

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndPNasJAEAfwVwk5KQjO5+7OPoCXHtubeJA2UEFUEu3zd91NbLXYmp4ybMhv/stMlssaBUGlntXIwTOm4uV9075V282uqbrT4bBvj9VEp+nF86lb76qJnGueY5gTEFYokUM6IQ2sUK9mj5lnr3nd7247cdG5xzmSZBwZeBR+bD6alJauPYhIxYOAg8dExTOzVDyt2+3Xg+YklzDozifiXaDfP15s2u54m+kbBVF8oVRkzL1+wtUEpwUfLqmRteCE2uNg3gru1D2OX8McoU8NOkwDvIYCS+76H9jHbJA4Z8NUgHMvZG9BRrpofWAXqUxMjW2AgXrYw1047/qVFSL3lKSwmWIzr4VySvfi/JHRRbHi8rCRbCpQXEI/1g2XYXH+dURCSFuw+gQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CaseID = _t, CaseNum = _t, #"CurrentAssignee (solver)" = _t, PreviousAssignee = _t, Timestamp = _t, CaseSequenceNo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CaseID", Int64.Type}, {"CaseNum", Int64.Type}, {"CurrentAssignee (solver)", type text}, {"PreviousAssignee", type text}, {"Timestamp", type datetime}, {"CaseSequenceNo", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"CaseID", Order.Ascending}, {"Timestamp", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"CaseID"}, { {"Details",  each Table.AddIndexColumn(_, "Steps",1,1), type table }}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"CaseNum", "CurrentAssignee (solver)", "PreviousAssignee", "Timestamp", "CaseSequenceNo", "Steps"}, {"CaseNum", "CurrentAssignee (solver)", "PreviousAssignee", "Timestamp", "CaseSequenceNo", "Steps"})
in
    #"Expanded Details"

2. Create a Sankey visual

Best Regards

[Anonymous]

Hello! Thanks for your report, unfortunately your pbix file is not multilevel. I want to see the full path a case travels.

If you know how i can solve that you would make my day 😄

[Charuta]

Hello.. Did you find a solution for this? I am having somewhat similar requirement.

Any suggestion/help on this?

Thanks in advance!