Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Find 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

 

Need Help

2 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Sherl ,

     

    I am not sure for some respects about the string text, One respect, the mark1 'content as picture below is "NPO-63928320-Robert Robert James-Some wild text to confused-ALL CAPS VALUES-6/31/19-DAH" or "NPO-63928320-Robert Robert James-6/31/19-DAH"; the other respect, the string text is not regular in structure such as mark2 and mark3, for the irregular structure, it is not supported to get entire name as you need completely using DAX.

     

     

     

     

    While, you can get name of regular structure string text by Spilt Column->By Delimiter ,spilt by "-" .

     

     

     

     

     

     

     

     

     

     

     

     

     

    Then, return the result showing picture below, the column Name.3 returns part name.

     

     

     

     

     

     

    Here is my test pbix file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EWB8lRsaBjlEuctkM9nsjckBtxP08jlhgR-owkZeNdVGfw?e=71JfL9

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Dax and PowerQuery seems to be not helping. Is there a preprocess plugin possible with PowerBI say Python or R incase of those missing functionalities to be implemented.