Obtain value from string with complex criteria in Power Query

[SOppeneer]

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 like to create a custom column multiplying the purchased quantity with the provided percentage to obtain the "pure quantity". The tricky part is obtaining the percentage as the material descriptions are free text and as such can be provided in several ways. Examples: 

... ##% ...

... ##.#% ...

... (##%) ...

... ##-##% ...

... ## % ...

##% ...

Ideally I would ignore the following:

... +-##% ...

... +- ##% ...

I think I know the steps to obtain the percentage value, but I don't know how to translate this to M-language. Can you help me figure this out? The steps:

1. Identify posStart = position of the % (I suppose this is Text.PositionOf([Material], "%"))

2. Identify posEnd = position of first of one of following characters more than 1 position in front of the %: {" ", "(", "-"} (0 if none in front)

3. Obtain number values (including decimals) between Start and End position (I suppose this is Text.Middle([Material], posStart, posEnd-PosStart))

I tried using Column from Example, but with the many criteria it just resulted in: if this material than this percentage, and so on.

[AlexisOlson · Accepted solution]

How about this?

Text.Middle(
    Text.Trim(Text.BeforeDelimiter([Column1], "%")),
    1 + Text.PositionOfAny(
            Text.Trim(Text.BeforeDelimiter([Column1], "%")),
            {" ", "(", "-"},
            Occurrence.Last
        )
)

[SOppeneer]

@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]

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

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

[SOppeneer]

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