Summary table of support ticket events

[Anonymous]

Hi everyone, I'm hoping someone can assist. I have a table of support ticket data, with 'ticket transfer' events in it (along with timestamps of those events, ticket Reference Numbers [RefNum], and a Customer Name), and I'm trying to transpose some of the columns into rows, in order to tell a clearer story about support ticket flows between support teams.

Support Ticket Data table example:

RefNum	Customer Name	DateActivityCreated	Transfer From	Transfer To
5	McDonands	10/19/2019 10:00 pm	Service Desk	Network Support
3	Amazon	10/19/2019 11:00 am	Service Desk	Database Support
3	Amazon	10/19/2019 11:03 am	Database Support	Server Support
3	Amazon	10/19/2019 11:08 am	Server Support	Database Support
2	Microsoft	10/19/2019 6:10 am	Service Desk	Server Support
2	Microsoft	10/19/2019 7:00 am	Server Support	Database Support
2	Microsoft	10/19/2019 7:10 am	Database Support	Service Desk
1	Google	10/19/2019 8:00 am	Service Desk	Desktop Support
4	IBM	10/19/2019 8:00 am	Service Desk	Network Support
4	IBM	10/19/2019 8:00 pm	Network Support	Network Support L2
1	Google	10/19/2019 8:07 am	Service Desk	Server Support
1	Google	10/19/2019 8:09 am	Server Support	Desktop Support
3	Amazon	10/19/2019 11:15 am	Database Support	Database Support L2

Here is the data that I would like to see:

RefNum	Customer Name	Transfer 1	Transfer 2	Transfer 3	Transfer 4	Transfer 5
1	Google	Service Desk	Server Support	Desktop Support
2	Microsoft	Service Desk	Server Support	Database Support	Service Desk
3	Amazon	Service Desk	Database Support	Server Support	Database Support	Database Support L2
4	IBM	Service Desk	Network Support	Network Support L2
5	McDonands	Service Desk	Network Support

Notes:

• There will be a variable amount of transfers in each RefNum. Some won't have any Transfers, and in a very worst-case scenario there might be 20 or more;
• There will be a variable maximum number of 'Transfer x' columns, to account for the variable number of ticket transfers per RefNum (I'm happy with blank cells if a particular RefNum doesn't have as many Transfers as another RefNum, though). If that is not possible, I'm happy to have a maximum of 20 'Transfer x' columns, and any remaining Transfers can just be cut off and not represented in the summary table;
• The data itself isn't in any particular order.

I'd really appreciate any assistance!

[Ashish_Mathur]

Hi,

I think your answer for Google is wrong.  This is what i think the result should be.  Let me know if i am correct so that i can share the solution file with you.

[Anonymous]

I do apologise @Ashish_Mathur , my answer for Google was indeed wrong. Thanks very much for responding, although the answer I'm looking for has now been provided, so I won't take up any more of your valuable time! 🙂

---

---

[Ashish_Mathur]

Hi,

You may download my solution workbook from here.

Hope this helps.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can also use a measure and matrix visual to meet your requirement. Firstly, we create a calculated table based on the max transport count of each Ref:

IndexColumnTable = 
ADDCOLUMNS (
    SELECTCOLUMNS (
        GENERATESERIES (
            1,
            MAXX (
                ADDCOLUMNS (
                    GROUPBY ( 'Table', 'Table'[RefNum] ),
                    "TransferCount", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[RefNum] = EARLIER ( [RefNum] ) )
                ),
                [TransferCount]
            ) + 1
        ),
        "Index", [Value]
    ),
    "TransferName", "Transfer " & [Index]
)

Then we create a measrue to show the transfer

ShowTransfer = 
VAR t =
    ADDCOLUMNS ( 'Table', "Index", RANKX ( 'Table', [DateActivityCreated],, ASC ) )
VAR t1 =
    FILTER (
        t,
        [Transfer From]
            <> MAXX ( FILTER ( t, [Index] = EARLIER ( [Index] ) + 1 ), [Transfer From] )
    )
VAR t2 =
    ADDCOLUMNS ( t1, "Index-2", RANKX ( t1, [Index],, ASC ) )
VAR i =
    MAX ( 'IndexColumnTable'[Index] )
RETURN
    IF (
        i
            <> COUNTROWS ( t2 ) + 1,
        MAXX ( FILTER ( t2, [Index-2] = i ), [Transfer From] ),
        MAXX ( FILTER ( t2, [Index-2] = i - 1 ), [Transfer to] )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Fantastic, thank you so much @v-lid-msft 🙂 Not only did you help me out perfectly, but you accounted for my unintended 'Google' error in the original specification 🙂

[v-lid-msft]

Hi @Anonymous ,

You are welcome, but if there won't have this situation in you data, you can use the following easier formula:

ShowTransfer = 
VAR t =
    ADDCOLUMNS ( 'Table', "Index", RANKX ( 'Table', [DateActivityCreated],, ASC ) )
VAR i =
    MAX ( 'IndexColumnTable'[Index] )
RETURN
    IF (
        i
            <> COUNTROWS ( t ) + 1,
        MAXX ( FILTER ( t, [Index] = i ), [Transfer From] ),
        MAXX ( FILTER ( t, [Index] = i - 1 ), [Transfer to] )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@Anonymouscan you please confirm if the data is wrongly typed for the following highlighted row

Should it not be "Desktop Support" and shouldn't the final table look as below

if yes, this is what the DAX query is

Table1 =
VAR _11 =
    SELECTCOLUMNS (
        Source,
        "@RefNum", Source[RefNum],
        "@Customer Name", Source[Customer Name],
        "@DateActivityCreated", Source[DateActivityCreated],
        "@Transfer", Source[Transfer From]
    )
VAR _12 =
    ADDCOLUMNS (
        ADDCOLUMNS (
            _11,
            "@Rank", RANKX (
                FILTER (
                    _11,
                    EARLIER ( [@RefNum] ) = [@RefNum]
                        && EARLIER ( [@Customer Name] ) = [@Customer Name]
                ),
                [@DateActivityCreated],
                ,
                DESC
            )
        ),
        "Final", IF ( [@Rank] = 1, 1 )
    )
VAR _13 =
    SELECTCOLUMNS (
        _12,
        "@RefNum", [@RefNum],
        "@Customer Name", [@Customer Name],
        "@DateActivityCreated", [@DateActivityCreated],
        "@Transfer", [@Transfer],
        "Final", [Final]
    )
VAR _1 =
    ADDCOLUMNS (
        ADDCOLUMNS (
            ADDCOLUMNS (
                DISTINCT (
                    SELECTCOLUMNS (
                        Source,
                        "@RefNum", Source[RefNum],
                        "@Customer Name", Source[Customer Name]
                    )
                ),
                "@DateActivityCreated", CALCULATE ( MAX ( Source[DateActivityCreated] ) )
            ),
            "@Transfer", LOOKUPVALUE (
                Source[Transfer To],
                Source[RefNum], [@RefNum],
                Source[Customer Name], [@Customer Name],
                Source[DateActivityCreated], [@DateActivityCreated]
            )
        ),
        "Final", 2
    )
VAR _x =
    UNION ( _13, _1 )
VAR _z =
    ADDCOLUMNS ( _x, "@DateActivityCreated2", [@DateActivityCreated] + [Final] )
VAR _y =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            _z,
            "Rank2", CONCATENATE (
                "Transfer",
                RANKX (
                    FILTER (
                        _z,
                        EARLIER ( [@RefNum] ) = [@RefNum]
                            && EARLIER ( [@Customer Name] ) = [@Customer Name]
                    ),
                    [@DateActivityCreated2],
                    ,
                    ASC
                )
            )
        ),
        "RefNum", [@RefNum],
        "Customer Name", [@Customer Name],
        "Date Activity Created", [@DateActivityCreated],
        "Transfer Value", [@Transfer],
        "Transfer", [Rank2]
    )
RETURN
    _y

[smpa01]

@Anonymous  please check if it is what you are looking for

let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Summary-table-of-support-ticket-events/m-p/820038#M394047", [WaitFor=[Timeout=#duration(0, 0, 0, 2)]]),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(1), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(1)"}, {"Column2", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(2), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(2)"}, {"Column3", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(3), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(3)"}, {"Column4", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(4), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(4)"}, {"Column5", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(5), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(5)"}}, [RowSelector="DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR, DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"RefNum", Int64.Type}, {"Customer Name", type text}, {"DateActivityCreated", type datetime}, {"Transfer From", type text}, {"Transfer To", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Name"}, {{"AD", each _, type table [RefNum=number, Customer Name=text, DateActivityCreated=datetime, Transfer From=text, Transfer To=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn(Table.RenameColumns(Table.RemoveColumns(Table.SelectRows(Table.AddIndexColumn(Table.Sort([AD],{{"DateActivityCreated", Order.Descending}}),"Rank",1)
, each ([Rank] = 1)),{"Transfer From", "Rank"}),{{"Transfer To", "Transfer"}})
,"Final", each "Final2")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"RefNum", "Customer Name", "DateActivityCreated", "Transfer", "Final"}, {"RefNum", "Customer Name", "DateActivityCreated", "Transfer", "Final"}),
    Custom1 = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn(Table.RenameColumns(Table.RemoveColumns(Table.SelectRows(Table.AddIndexColumn(Table.Sort([AD],{{"DateActivityCreated", Order.Descending}}),"Rank",1)
, each ([Rank] <>0)),{"Transfer To"}),{{"Transfer From", "Transfer"}})
, "Final", each if [Rank]=1 then "Final1" else null)),
    #"Removed Other Columns1" = Table.SelectColumns(Custom1,{"Custom"}),
    #"2" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"RefNum", "Customer Name", "DateActivityCreated", "Transfer", "Final"}, {"RefNum", "Customer Name", "DateActivityCreated", "Transfer", "Final"}),
    Custom2 = #"1"&#"2",
    #"Filtered Rows" = Table.SelectRows(Custom2, each ([RefNum] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Customer Name", Order.Ascending}, {"DateActivityCreated", Order.Ascending}, {"Final", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"DateActivityCreated"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "T1", each "Transfer"),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom1"),
    #"Grouped Rows1" = Table.Group(#"Removed Duplicates", {"Customer Name"}, {{"Count", each _, type table [RefNum=number, Customer Name=text, Transfer=text, T1=text]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Removed Other Columns2" = Table.SelectColumns(#"Added Custom2",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns2", "Custom", {"RefNum", "Customer Name", "Transfer", "T1", "Index"}, {"RefNum", "Customer Name", "Transfer", "T1", "Index"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US"),{"T1", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Transfer"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,"",Replacer.ReplaceValue,{"Transfer3", "Transfer4", "Transfer5"})
in
    #"Replaced Value"

[Anonymous]

Thanks for the response @smpa01, much appreciated - would it be possible to provide that solution in an Excel spread sheet & upload via the PBIX file? It would be easier for me to unpack the solution and replicate it, that way?