Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey good people,
I am trying to get the pack number from a table as below :
Towards this end, I wrote a code as below, which works part of the way :
if Text.Contains([Title],"pack",Comparer.OrdinalIgnoreCase) and not Text.Contains([Title],")") then Text.End([Title],1) else 1
But where the string "Pack" does not occur at the end of the [Title] string, then it does not return the required number but returns the character which is there at the end of the string.
Is there a way, I can isolate the number coming after the string "pack of", irrespective where this string occurs within the [title]?
Any help much appreciated.
best
Solved! Go to Solution.
Test this and let me know if there is any exception. If yes, I will need that data where you get exception
try Number.From(Text.Split(Text.Split([Title],"Pack of"){1}," "){1}) otherwise 1
You can just use "of " and " |" as delimiters, since as you know,
Text.BetweenDelimiters(text as nullable text, startDelimiter as text, endDelimiter as text, optional startIndex as any, optional endIndex as any) as any
So = Table.AddColumn(PriorStepOrTableName, "Quantity", each Text. Between Delimiters([ColumnName], "of ", " |"), type text)
But listen, it appears that this is a table with one column. But if "Title" is the name of your single table column, then you are good.
--Nate
it should pick up the number occuring right after the space after "of"
Test this and let me know if there is any exception. If yes, I will need that data where you get exception
try Number.From(Text.Split(Text.Split([Title],"Pack of"){1}," "){1}) otherwise 1
@Vijay_A_Verma - Thanks for the quick revert.
However , the formula is probably not playing out fully and returning this error :
[Expression.Error] There weren't enough elements in the enumeration to complete the operation.
How can I send you a link for the [Title] Column from the table?
@Vijay_A_Verma - Vijay, I ran it again, and this time it worked. I most certainly used "" instead of " ", by oversight. Thanks a lot.
What is the expected answer for these scenarios when following appears at the end
Pack of
Pack of 3 x 150gms
Pack of 2 (2 x 150 gms)