so you get a blank list {}, so {}{0} is error, then {}{0}{1} is error.
can add a ? to tolerate the error, when there is no item on the index you want, it will give a null
Hey @Daryl-Lynch-Bzy ,
Below is a snapshot of the text column :
And here is the code I am using for extracting the key words like Stevia or Aspartame and I use similar code to extract the pack size of type (like sachets or tablets) :
Table.AddColumn(Custom2, "Ingredient", each if
Text.Contains([Title],"50%",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"sugarlo",Comparer.OrdinalIgnoreCase)then "SugarLo" else
if Text.Contains([Title],"Stevia",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"Green",Comparer.OrdinalIgnoreCase)
then "Stevia" else
if Text.Contains([Title],"classic",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"cook",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"zero calorie",Comparer.OrdinalIgnoreCase) then "Sucralose" else
if Text.Contains([Title],"original" ,Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"Low Calories", Comparer.OrdinalIgnoreCase)
then "Aspartame" else
if Text.Contains([Title],"xylitol",Comparer.OrdinalIgnoreCase) then "Xylitol" else null)
This code keeps getting longer and more complex with every refresh as the texts start getting new additions everytime so I have to mody this code post every refresh.
But what remains invariant is the list of actives or the list of pack sizes or the type etc - hence if I can work off a list, it will be a simple task just to update the relevant list
Thanks and appreciate the leg-up
The source data remains same...
The code I am using to extract the form is as below :
Table.AddColumn(Ingredients, "Form", each if Text.Contains([Title],"tablets",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"tablet",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"500 pcs",Comparer.OrdinalIgnoreCase)
or Text.Contains([Title],"330",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"440",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"550",Comparer.OrdinalIgnoreCase)
then "Tablets" else
if Text.Contains([Title],"sachet",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"sachets",Comparer.OrdinalIgnoreCase) then "Sachets" else
if Text.Contains([Title],"500 g",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"500g",Comparer.OrdinalIgnoreCase)
or Text.Contains([Title],"xylitol",Comparer.OrdinalIgnoreCase) then "Pouch" else
if Text.Contains([Title],"80",Comparer.OrdinalIgnoreCase) or
Text.Contains([Title],"150g",Comparer.OrdinalIgnoreCase) or
Text.Contains([Title],"150 g",Comparer.OrdinalIgnoreCase) or
Text.Contains([Title],"150gm",Comparer.OrdinalIgnoreCase) or
Text.Contains([Title],"150 GRAM",Comparer.OrdinalIgnoreCase)
then "Jar" else "Tablets"
)The code I am using to extract the sizes is as below :
a. First I remove all text from the [Title] string :
Table.AddColumn(MPU, "Remove Text", each Text.Remove([Title],{"A".."Z","a".."z"}))b. This returns a column as below :
c. from this column now, I feed a pre-prepared list :
into the following code to extract the matching strings :
Table.AddColumn(RemoveTextFromTitle, "Pack", each List.Select(PackList,
(x)=>
Text.Contains([Remove Text],x,Comparer.OrdinalIgnoreCase)
){0}?)d. This returns a column as below :
e. I now clean up the above the column, by feeding another list with corrected packlist :
by using the following code :
List.Accumulate({0..Table.RowCount(PackSize)-1},
PackPosition,
(x,y)=>
Table.ReplaceValue(x,
PackSize[OldValue]{y}, PackSize[NewValue]{y},
Replacer.ReplaceValue, {"Pack"})
)Little long-winded, but I could not find a simpler way to get to this in fewer steps.
Any help appreciated.
Sorry I confused you unintentionally. let me try and simplify this for you :
1. We are starting with the same column as above :
2. This text string, contains terms like sachets, tablets and jars - which are the forms. I want to extract these sub-strings from this column and the desired output should be to add a column headed "Form" with the strings as below :
3. Next if you notice, the text string column also includes number strings as - 100,300, 500, 80,50 etc. These are pack sizes. The desired output would be to extract these strings into a separate column headed "pack Size" :
Not sure if that has simplified things a bit and helps you better with the solution I am seeking.
@wdx223_Daniel , I tried the code above only for ingredients :
Table.SplitColumn(ActiveNewWay.1, "Title", each
let
fx=(x,y)=>List.RemoveItems(Splitter.SplitTextByAnyDelimiter(x)(y),{"",null}),
Ingredient=List.Skip(
{{{"50%","sugarlo"},"SugarLo"},
{{"Stevia","Green"},"Stevia"},
{{"classic","cook","zero calorie"},"Sucralose"},
{{"original","Low Calories"},"Aspartame"},
{{"xylitol"},"Xylitol"}
},
(x)=>not List.Contains(x{0},_,(x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))
){0}?{1}?
// Form=fx(fx(YourFormList,_),_){0}?,
// PackSize=fx(fx(YourPackSizeList,_),_){0}?,
// MultiPack=fx(fx(YourMulitPackList,_),){0}?
in {Ingredient}
{"Ingredient"})And this is returning the following error :
Am I missing something here?
regds.,
so you get a blank list {}, so {}{0} is error, then {}{0}{1} is error.
can add a ? to tolerate the error, when there is no item on the index you want, it will give a null
Helpful resources
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
