Forum Discussion
Editing data with several status codes in the same column
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.
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.
1 Reply
- HotChilli
Community Champion
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.