New column based on a text filter of single number but only the number next to a keyword :/

[Mullz]

HI all,

Not sure if this is possible but I have a raft of projects getting pulled in from project for the web:

MFA - Workstream 1 (DPG3-SW021)

ITSM SD system (DPG1-PR012)

AV Work - Workstream 1 (DPG1-PR020)

I need to extract what programme this work is in so the number after the DPG can be 1/2/3/4 which corresponds to different programmes.

I've tried using Filter but I don't think that's what I need as that's only returning true/false which wont help if I want a column which has Programme 1/2/3/4

Any ideas?

Thanks

David

[Mullz · Accepted solution]

This is resolved, just doing more digging in power query and found the 'Extract' function and also the 'Replace values' function to also change 'DPG1' to the actual programme name!

[Adescrit]

There are probably a few ways to do this. One could be in Power Query - Split the column by delimiter "DPG" to create a new column containing your project number.

Alternatively create a calculated column in your table, and use this new column as a filter. DAX for the calculated column to return the project number:

Project Number = 
VAR __Start =  FIND("DPG",'Work'[Projects])
VAR __Number = VALUE( MID( 'Work'[Projects], __Start + 3, 1))
RETURN
    __Number