Editing data with several status codes in the same column

[Anonymous]

I want to analyse the reason some transactions fails. I have two datasets that are relevant for this. One dataset containing the following:

Code	Description
1	Transaction completed
2	Canceled by customer
3	Failed due to XXX
4	Failed due to XXX
5	Failed due to XXX
6	Failed due to XXX
7	Failed due to XXX

In the other dataset that relates to the data above I have this info:

Session	Timestamp	Machine-ID	Transaction status	Status code transaction
1000XXX	XX.XX.20	120XX	Completed	[1]
1000XXX	XX.XX.20	120XX	Failed	[2, 7]
1000XXX	XX.XX.20	120XX	Completed	[1]
1000XXX	XX.XX.20	120XX	Failed	[3, 6]
1000XXX	XX.XX.20	120XX	Failed	[3, 4, 7]
1000XXX	XX.XX.20	120XX	Failed	[2, 6]

I want to get some insight into what is the cause of the failed transactions using similar data as above I get results like this in my report: 

As you can see PowerBI does not sum every failed transaction with status code [2] together, but it sums all the failed transactions with the same status code transactions (i.e. [3, 7]). For some transactions there are only one reason for the transaction failing, as you can see the most repeating status code on failing transactions is [3]. 

What I would like to do is to edit my data so that I can get the sum of for example [2] alone and [3] alone. 

[HotChilli · Accepted solution]

If you remove the '[' and ']' from the Status code transaction column (using 'replace values' in Power Query).

Then Split the column (by comma).

Then select the first 4 columns (the non-status columns) and choose 'Unpivot other columns' from Transform menu.

This gives the columnar form of data that powerbi likes.

You can relate the two tables via 1-to-many on Code and Value.

In the visual in Report view, I think you plot the Code v Count of Value.  Use a slider on Code from the dimension table to filter Status codes.