Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
  • HotChilli's avatar
    HotChilli
    6 years ago

    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.

     

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
6 years ago

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.