Parse Table Column for Bar Chart - SQL Server in PowerBI

Question

I am working in PowerBI (july 2018 edition) and I am pulling data from SQL Server.

I would like to create a bar chart that lists the count of tickets for each Name. If there are multiple resources for a ticket, that ticket count applies for all those names.

My source table is like the following.

Ticket Number | Resources
1234            Name1, Name2, Name3, ... ,  Name N          
1238            Name2, Name3 
1240            Name4, Name5

gotqn gotqn · Accepted Answer · 2018-08-08T05:31:35

You need to split the values and then perform counting. If you do not have split function and there is not built-in such in your SQL Server Edition check this CLR integration.

Otherwise, here is pure T-SQL solution:

DECLARE @DataSource TABLE
(
    [TicketID] INT
   ,[Resources] VARCHAR(MAX)
);

INSERT INTO @DataSource ([TicketID], [Resources])
VALUES (1234, 'Name1, Name2, Name3,  NameN')
      ,(1238, 'Name2, Name3 ')
      ,(1240, 'Name4, Name5');

WITH DataSource ([TicketID], [Resources]) AS
(      
    SELECT [TicketID]
          ,CAST(N'<r><![CDATA[' + REPLACE([Resources], ',', ']]></r><r><![CDATA[') + ']]></r>' AS XML)
    FROM @DataSource         
), DataSourceNormalized ([Resource], [TicketID]) AS
(
    SELECT RTRIM(LTRIM(Tbl.Col.value('.', 'varchar(250)')))
          ,[TicketID]
    FROM DataSource
    CROSS APPLY [Resources].nodes('//r') Tbl(Col)
)
SELECT [Resource]
      ,COUNT([TicketID]) AS [Count]
FROM DataSourceNormalized
GROUP BY [Resource];

The first CTE cast the values to XML in order to be able to split them. The second normalize the data:

Then, simply aggregation is performed:

If your working with huge amount of data you this can be slow. If you are looking for performance you need to implement CLR split function or even better - normalize your data in order to be ready for COUNT.

Parse Table Column for Bar Chart - SQL Server in PowerBI

1 Answers