Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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:

 

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

 

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

 

SessionTimestampMachine-IDTransaction statusStatus code transaction
1000XXXXX.XX.20120XXCompleted[1]
1000XXXXX.XX.20120XXFailed[2, 7]
1000XXXXX.XX.20120XXCompleted[1]
1000XXXXX.XX.20120XXFailed[3, 6]
1000XXXXX.XX.20120XXFailed[3, 4, 7]
1000XXXXX.XX.20120XXFailed[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: 

failed transactions.png

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. 

 

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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.

 

View solution in original post

1 REPLY 1
HotChilli
Super User
Super User

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.

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.