• 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
GraH - Guido said:
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
Click to expand...
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?
 
Brunda said:
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?
Click to expand...
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".
 
GraH - Guido said:
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".
Click to expand...
hello,

May i know that step what has to be done so i'll add the same to it
 
GraH - Guido said:
See the step CleanList, same as for the measures.
Click to expand...
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
You must log in or register to reply here.
Back
Top