Forum Discussion

Zabeer's avatar
Zabeer
Icon for Helper I rankHelper I
5 years ago
Solved

Extract field value in Power Query to a new column based on conditions

Hello everyone,

I wish to extract specific values in a column to a new column. The rule is that the values need to start with 75 or 76 and can have maximum of 10 digits. Examples below should make it clear.

InputResult
PO: 7500013234 - SO -128327500013234
SO117934 PO-75000123127500012312
PN; 15800-024A/7600012040  7600012040
PO7500012030 -SO-123127500012030

 

Kindly help. I would need this on Power Query.
Thank you.

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    Zabeer,

     

    Try this custom column in Power Query:

     

    if Text.PositionOf([Input], "75") <> - 1 then
  Text.Middle([Input], Text.PositionOf([Input], "75"), 10)
else if Text.PositionOf([Input], "76") <> - 1 then
  Text.Middle([Input], Text.PositionOf([Input], "76"), 10)
else
  null

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    Zabeer,

     

    Try this custom column in Power Query:

     

    if Text.PositionOf([Input], "75") <> - 1 then
  Text.Middle([Input], Text.PositionOf([Input], "75"), 10)
else if Text.PositionOf([Input], "76") <> - 1 then
  Text.Middle([Input], Text.PositionOf([Input], "76"), 10)
else
  null