Forum Discussion

SOppeneer's avatar
SOppeneer
Frequent Visitor
4 years ago
Solved

Obtain value from string with complex criteria in Power Query

Hi Everyone,   I have a dataset with material descriptions and their purchased quantities, where some material descriptions mention a percentage of "pure material" (e.g. without water). I would lik...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    How about this?

     

    Text.Middle(
    Text.Trim(Text.BeforeDelimiter([Column1], "%")),
    1 + Text.PositionOfAny(
            Text.Trim(Text.BeforeDelimiter([Column1], "%")),
            {" ", "(", "-"},
            Occurrence.Last
        )
)
SOppeneer's avatar
SOppeneer
Frequent Visitor
4 years ago

AlexisOlson Thanks for your feedback! Your provided solution seems to correctly identify the percentage from the Material description. The only comment I have is that it also returns a result if there is no "%" in the Material description (i.e.: it gives me the last word/number). How would I best adjust the formula to result in 100 (as in 100%) if no "%" exists?

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

The simplest modification would be to check for "%" first.

if Text.Contains([Column1], "%") then [previous code] else 100

 

  • SOppeneer's avatar
    SOppeneer
    Frequent Visitor
    4 years ago

    AlexisOlson Thanks, I should have been able to figure that out. 🙂