Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |