Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
How about this?
Text.Middle(
Text.Trim(Text.BeforeDelimiter([Column1], "%")),
1 + Text.PositionOfAny(
Text.Trim(Text.BeforeDelimiter([Column1], "%")),
{" ", "(", "-"},
Occurrence.Last
)
)
How about this?
Text.Middle(
Text.Trim(Text.BeforeDelimiter([Column1], "%")),
1 + Text.PositionOfAny(
Text.Trim(Text.BeforeDelimiter([Column1], "%")),
{" ", "(", "-"},
Occurrence.Last
)
)
@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?
The simplest modification would be to check for "%" first.
if Text.Contains([Column1], "%") then [previous code] else 100
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.