• 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

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

  • Chandoo44721-book5-CustomFx.xlsx
    79.1 KB · Views: 5
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?
 
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".
 
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
 
See the step CleanList, same as for the measures.
Hello,
Hope you're doing good.
There is few requirement and changes in query, kindly help us with it
(changes required is mentioned in sheet 1 tab in the attached file)
 

Attachments

  • CustomFx (5)(2).xlsx
    741.4 KB · Views: 2
Back
Top