Extract numbers depending on letters in front of them

[Potzten]

Hello guys,

since I am completely new to the Power BI game, I have lots and lots of questions, but let's start with one:

I am working with Maven example data, in this case the world cup data that shows all different types of information about all worldcups from 1934 to 2014.

Inside the data there's a Player-Table with an event-column. An example for that event column could be: "G5' G24' G37' Y39' MP56' O82'" meaning: this player scored 3 goals (in minutes 5, 24 and 37), got a yellow card in minute 39, missed a penalty in minute 56 and got taken out in minute 82 (taken out could also be flagged by the tag "OH45'" meaning, player got taken out during halftime).

To analyze e.g. total played times of players I want this column to be split:
I want to have a goal column that gives the minutes of the goals scored (e.g. "5, 24, 37"), I want a taken out column (here it would be "82") also I want a taken in column and some for yellow cars, red cards, penalties etc. . 

So I need columns that show me those numbers that appear directly after a given letter up to a blank space. Do you have any idea how I can achieve that?

I hope that what I wrote makes sence to you 😄

Cheers and thanks!

[Shaurya]

Hi @Potzten,

I get what you are trying to do. If I'm not wrong, you are trying to do something like this:

Goals = IF(LEFT('Table'[Event],1)="G",MID('Table'[Event],2,LEN('Table'[Event])))

Yellow Cards = IF(LEFT('Table'[Event],1)="Y",MID('Table'[Event],2,LEN('Table'[Event])))

Missed Penalty = IF(LEFT('Table'[Event],2)="MP",MID('Table'[Event],3,LEN('Table'[Event])))

Taken Out = IF(LEFT('Table'[Event],1)="O",MID('Table'[Event],2,LEN('Table'[Event])))

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: How to Export Telemetry Data from Azure IoT Central into Power BI

[Potzten]

Heyho,

thanks for your answer ! Well, this is kind of what I want. Since I want one line for each player, I need it to be like:
|    Goals    | Yellow Cards | Missed Penalty | Taken Out |
|5, 37, 65    | 78                 | 51                     | 87             |

So that all of those "G-Flagged" Numbers get in the same column and so on - do you think this is possible?