Virtual table to aggregrate support ticket activity logs

[MichaelHutchens]

Hi folks, I'm hoping someone can help. I have a data table that contains rows of IT support tickets, the Assignment Groups that the tickets were transferred to (one 'transfer activity' per row), and the timestamp of when the ticket transfer happened. An example is below:

Number	Transfer	Created
INC0184914	Business Systems	02/04/2020 15:14:40
INC0184914	Business Systems	30/03/2020 11:31:45
INC0184914	Info Apps	18/02/2020 15:24:16
INC0184914	Info Apps	04/02/2020 16:06:17
INC0184914	Infrastructure Team	30/03/2020 13:46:50
INC0173401	Infrastructure Team	17/02/2020 10:37:27
INC0200144	Service Site 1	20/02/2020 10:04:25
INC0200144	Service Site 2	07/02/2020 14:03:45
INC0173401	Service Site 3	12/02/2020 11:33:20
INC0200144	Service Site 6	04/02/2020 15:27:37

(the live data is in a random order in this table)

I'd like to transform this data into a virtual table so that:

1. There is a single row for each IT support ticket
2. The columns in the virtual table include the ticket number, and, in chronological order, the names of the Assignment Groups that each ticket has been transferred to. This will give me a visualization of the 'path' of support tickets in the organization. An example of what I'd like to see is below:

Number	Transfer 1	Transfer 2	Transfer 3	Transfer 4	Transfer 5
INC0200144	Service Site 6	Info Apps	Service Site 2	Info Apps	Service Site 1
INC0184914	Info Apps	Info Apps	Business Systems	Infrastructure Team	Business Systems
INC0173401	Service Site 3	Infrastructure Team

The number of 'transfer' columns would need to expand and contract, depending on the maximum number of ticket transfers.

Ideally I'd like to be able to export data from this virtual table into a .CSV in the same format as the example result table above.

Any help would really be appreciated 🙂

[parry2k]

@Ashish_Mathur This is great but I will not go that route. Pivot is a very expensive query and it will take forever to work. Cheers!!

[Ashish_Mathur]

Thank you for the feedback @parry2k 

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZCxDoIwFEV/pWGmyXt9FbCbOrG44EYYCKkJA0hoMfHvbRMFBAxrc0/PfTfPg/R6AUzkEWUQBufB1K02hmUvY3Vj3BMIDpILEMDwoFyqCPchAg70gVARrqG0vT/Yqet8GhPuLF+F2FDM067NmI4URJvpvjS2Hyo79JrddNksOpGSMy4mCfiXw3jygaJ45AQASu/LdP+sK82y2mrmPxIwR0DuIMJfNbNIBbRu94OQLyYmxI1MO5ZosZ1b2t1SvAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Transfer = _t, Created = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", type text}, {"Transfer", type text}, {"Created", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Number", Order.Ascending}, {"Created", Order.Ascending}}),
    Partition = Table.Group(#"Sorted Rows", {"Number"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Transfer", "Created", "Index"}, {"Transfer", "Created", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each "Transfer "&Number.ToText([Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Created"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Transfer")
in
    #"Pivoted Column"

Hope this helps.

[parry2k]

@MichaelHutchens glad, I could help. Good luck!

[parry2k · Accepted solution]

@MichaelHutchens add new column using following DAX expression:

Rank Transfer = 
RANKX ( 
    FILTER ( 
        'Table', 
        'Table'[Number] = EARLIER ( 'Table'[Number] ) 
    ), 
    'Table'[Created] , , 
    ASC, 
    Dense 
)

Add a matrix visual, user number on rows, rank transfer on columns, and Transfer on values

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[MichaelHutchens]

Hi @parry2k, thanks so much for this. Aside from being able to export the results into .CSV (and that is a 'nice to have'), this fits the bill really well 🙂

[parry2k]

@MichaelHutchens why INC0184914 will not show "Info Apps"?

[MichaelHutchens]

Hi @parry2k , apologies that was my error. I've edited the original post to correct this now 🙂