Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Extract Names between Delimiter

I am totally new to PowerBI and I was someone who coded all throughout the life but I am trying to replicate something similar with DAX and can someone help me here?

 

I am trying to extract names from the below string of text the idea is to

  • split them as array based on delimiter (-)
  • Skip all caps in those array of values
  • Sort values based on the one that has most spaces (<space>)
  • Look for first character capitalization (Regex match) in those array values
  • Return the one that has the matches these requirement.

 

 

Say, if I take, NPO-63928320-Robert Robert James-6/31/19-DAH and here how it would processed in each step

[NPO,63928320,Robert Robert James,Some wild text to confused,ALL CAPS VALUES,6/31/19,DAH]

[NPO,63928320,Robert Robert James,Some wild text to confused,6/31/19,DAH]

[Some wild text to confused,Robert Robert James,NPO,63928320,6/31/19,DAH]

[Robert Robert James,NPO,63928320,6/31/19,DAH]

Returns (Robert Robert James)

 

NPO-63928320-Robert Robert James-Some wild text to confused-ALL CAPS VALUES-6/31/19-DAH
NPO-39528320-Li Wang-06/24/2017-SHA
NPO-39285320-Xi Nam Suas Aasq-07/23/2017-EKA
NPO-3928e320-Rich Braden-05/21/2014-KAM
NPO-Justine Schimpf-392y8320-08/21/16-SHAM
NPO-39283g20-Nadla Schimpf-02/22/17-ASQ
JAPB-39283320-Wang Shang-09/08/2013-ASQ
ACDBPO-Raq Rich-02/07/13-36928320-SAW
EUAS-392885320-Jillan Dang Romeri Hernadaez-01/07/2017-SLA

 

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support

    Hi Anonymous 

    Could you accept other methods such as using Power Query to transform the text string?

     

    Best Regards

    Maggie