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 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.

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
        )
)

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    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
        Super User
        4 years ago

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

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