Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Extract number with condition

Hello,

 

I have this column :

 

DFS-PI1

DFS-PI2

01234

P393

BLABLABLA

 

And I want to create a column that extract the last number only if it starts with "DFS-PI", otherwise show null. The result would be:

1

2

null

null

null

 

 

I can't find a way to do that. Anyone can help me please?

Thank you

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Anonymous ,

     

    In Power Query you would create a new column with the following calculation:

    if
  Text.StartsWith([yourColumnName], "DFS-PI")
then
  Text.End([yourColumnName], 1)
else
  null

     

    Pete

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Anonymous ,

     

    In Power Query you would create a new column with the following calculation:

    if
  Text.StartsWith([yourColumnName], "DFS-PI")
then
  Text.End([yourColumnName], 1)
else
  null

     

    Pete

  • jppv20's avatar
    jppv20
    Solution Sage
    4 years ago

    Hi Anonymous 

     

    This should work:

    Extract = IF(LEFT('Table'[Column],6)="DFS-PI",RIGHT('Table'[Column],1),BLANK())
     
    Jori