• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

quantity

GraH - Guido

Well-Known Member
Hi, Brunda,

Good you follow up. #24 contained the extra information required on the existing measures.
Notice a simple question turned into a (be it small) agile development. Last time I checked minions only exist in the movie. We are no free resources.

This is my last attempt at this.
Code:
let GetMeasures = (Text as text) as record =>
   
    let
          LCase = Text.Lower(Text)
        , ListChars = Text.ToList(LCase)
        , ReplaceChars = List.ReplaceMatchingItems
                    ( ListChars
                    ,{ { "(" , " " }
                    ,  { ")" , " " }
                    ,  { "{" , " " }
                    ,  { "}" , " " }
                    ,  { "[" , " " }
                    ,  { "]" , " " }
                    ,  { "|" , " " }
                    ,  { "\" , " " }
                    ,  { "/" , " " }
                    ,  { "," , " " }
                    ,  { "%" , "% " }
                    ,  { "-" , " " }
                    ,  { "x" , " " }
                     }
                    )
        , NewTxt = Text.Combine(ReplaceChars, "")
        , AsLst = Text.Split(NewTxt, " ")
        , CleanList = List.Select(AsLst, each
                         Text.Select(_,{"%"}) = ""
                      and Text.Contains(_, "bc") = false
                      //and Text.Contains(_, "x") = false
                      and (
                         Text.StartsWith(_, "0")
                      or Text.StartsWith(_, "1")
                      or Text.StartsWith(_, "2")
                      or Text.StartsWith(_, "3")
                      or Text.StartsWith(_, "4")
                      or Text.StartsWith(_, "5")
                      or Text.StartsWith(_, "6")
                      or Text.StartsWith(_, "7")
                      or Text.StartsWith(_, "8")
                      or Text.StartsWith(_, "9")
                      or Text.StartsWith(_, ".")
                      or List.ContainsAny({_},
                           { "gm"
                           , "gms"
                           , "grm"
                           , "grms"
                           , "gram"
                           , "grams"
                           , "g"
                           , "kg"
                           , "kgs"
                           , "Kilogram"
                           , "Kilograms"
                           , "ml"
                           , "millilitre"
                           , "millilitres"
                           , "litre"
                           , "litres"
                           , "l"
                           , "o.z."
                           , "oz"
                           , "oz."
                           , "fl oz"
                           , "fl.oz"
                           , "lb"
                           , "lbs"
                            }
                           )
                      )  
                        )
        , AddSpace = List.Transform(CleanList, each
                          Text.Replace( _ , Text.Select(_, {"0".."9", "."}) , " " & Text.Select(_, {"0".."9", "."})  )
                        )
        , PipeList = List.Transform(AddSpace, each
                            Text.Replace( _ , Text.Select(_, {"a".."z"}) , "|" & Text.Select(_, {"a".."z"})  )
                        )
        , RestoreTxt = Text.Combine(PipeList, ";")
        , CleanTxt = Text.Replace(RestoreTxt, ";|","|")
        , NewList = Text.Split(CleanTxt, ";")
        , SelectMeasures = List.Select(NewList, each Text.Select(_,"|") <>"" and Text.Select(_, {"0".."9"}) <> "" )
        , Counter = List.Count(SelectMeasures)
        , MakeRecord = [ Quantity = if Counter = 0
                                    then "NA"
                                    else Text.Select(SelectMeasures{0}, {"0".."9", "."})
                        , Measure = if Quantity = "NA" then "NA"
                                   else Text.Select(SelectMeasures{0}, {"a".."z"})
                        , #"Quantity-Measure" = if Quantity = "NA" then "NA" else Quantity& " " &Measure
                        , #"Multiple Measures" = if Counter > 1
                                            then Text.Replace(Text.Combine(SelectMeasures, " , "), "|"," ")
                                            else "NA"
                        ]
    in
        MakeRecord

in
    GetMeasures
 

Attachments

Brunda

Member
Hi, Brunda,

Good you follow up. #24 contained the extra information required on the existing measures.
Notice a simple question turned into a (be it small) agile development. Last time I checked minions only exist in the movie. We are no free resources.

This is my last attempt at this.
Code:
let GetMeasures = (Text as text) as record =>
  
    let
          LCase = Text.Lower(Text)
        , ListChars = Text.ToList(LCase)
        , ReplaceChars = List.ReplaceMatchingItems
                    ( ListChars
                    ,{ { "(" , " " }
                    ,  { ")" , " " }
                    ,  { "{" , " " }
                    ,  { "}" , " " }
                    ,  { "[" , " " }
                    ,  { "]" , " " }
                    ,  { "|" , " " }
                    ,  { "\" , " " }
                    ,  { "/" , " " }
                    ,  { "," , " " }
                    ,  { "%" , "% " }
                    ,  { "-" , " " }
                    ,  { "x" , " " }
                     }
                    )
        , NewTxt = Text.Combine(ReplaceChars, "")
        , AsLst = Text.Split(NewTxt, " ")
        , CleanList = List.Select(AsLst, each
                         Text.Select(_,{"%"}) = ""
                      and Text.Contains(_, "bc") = false
                      //and Text.Contains(_, "x") = false
                      and (
                         Text.StartsWith(_, "0")
                      or Text.StartsWith(_, "1")
                      or Text.StartsWith(_, "2")
                      or Text.StartsWith(_, "3")
                      or Text.StartsWith(_, "4")
                      or Text.StartsWith(_, "5")
                      or Text.StartsWith(_, "6")
                      or Text.StartsWith(_, "7")
                      or Text.StartsWith(_, "8")
                      or Text.StartsWith(_, "9")
                      or Text.StartsWith(_, ".")
                      or List.ContainsAny({_},
                           { "gm"
                           , "gms"
                           , "grm"
                           , "grms"
                           , "gram"
                           , "grams"
                           , "g"
                           , "kg"
                           , "kgs"
                           , "Kilogram"
                           , "Kilograms"
                           , "ml"
                           , "millilitre"
                           , "millilitres"
                           , "litre"
                           , "litres"
                           , "l"
                           , "o.z."
                           , "oz"
                           , "oz."
                           , "fl oz"
                           , "fl.oz"
                           , "lb"
                           , "lbs"
                            }
                           )
                      ) 
                        )
        , AddSpace = List.Transform(CleanList, each
                          Text.Replace( _ , Text.Select(_, {"0".."9", "."}) , " " & Text.Select(_, {"0".."9", "."})  )
                        )
        , PipeList = List.Transform(AddSpace, each
                            Text.Replace( _ , Text.Select(_, {"a".."z"}) , "|" & Text.Select(_, {"a".."z"})  )
                        )
        , RestoreTxt = Text.Combine(PipeList, ";")
        , CleanTxt = Text.Replace(RestoreTxt, ";|","|")
        , NewList = Text.Split(CleanTxt, ";")
        , SelectMeasures = List.Select(NewList, each Text.Select(_,"|") <>"" and Text.Select(_, {"0".."9"}) <> "" )
        , Counter = List.Count(SelectMeasures)
        , MakeRecord = [ Quantity = if Counter = 0
                                    then "NA"
                                    else Text.Select(SelectMeasures{0}, {"0".."9", "."})
                        , Measure = if Quantity = "NA" then "NA"
                                   else Text.Select(SelectMeasures{0}, {"a".."z"})
                        , #"Quantity-Measure" = if Quantity = "NA" then "NA" else Quantity& " " &Measure
                        , #"Multiple Measures" = if Counter > 1
                                            then Text.Replace(Text.Combine(SelectMeasures, " , "), "|"," ")
                                            else "NA"
                        ]
    in
        MakeRecord

in
    GetMeasures
Hello,
This works good. Add extra words if necessary for us in the part of where you have added gm to lbs
It works right?
 

GraH - Guido

Well-Known Member
Hello,
This works good. Add extra words if necessary for us in the part of where you have added gm to lbs
It works right?
Indeed, you got that right.
Now you might need to extend the step where I exclude "bc" as a measure too. I could not see another way of dealing with those patterns. Hoping you don't have a lot of those "exceptions".
 

Brunda

Member
Indeed, you got that right.
Now you might need to extend the step where I exclude "bc" as a measure too. I could not see another way of dealing with those patterns. Hoping you don't have a lot of those "exceptions".
hello,

May i know that step what has to be done so i'll add the same to it
 
Top