• 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

Brunda

Member
hi chandoo,

Hope your doing good,

i want to know if a macro can be created to pick quantity from title

eg:
Assorted Snack/Bars - 210 GMS (pick only 210 and GMS in seperate columns)
Skin Face Serum - 30 ml (pick only 30 and ml in seperate columns)
 

AlanSidman

Well-Known Member
Can be achieved with Power Query.

Here is the Mcode and file is attached for your reviewl

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({"- "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"})
in
    #"Split Column by Delimiter"
 

Attachments

Brunda

Member
Can be achieved with Power Query.

Here is the Mcode and file is attached for your reviewl

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({"- "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"})
in
    #"Split Column by Delimiter"
Hi Alan,
It works good but there would be different forms of title from which the workbook provided by you is not fetching i have added few title to it kindly check if it could work in that way as well.
 

Attachments

AlanSidman

Well-Known Member
Because you don't have consistent delimeters or layout, the following is the best I can do for you. Hopefully, someone with more skill set can assist here

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition","}","",Replacer.ReplaceText,{"Column1.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",")","",Replacer.ReplaceText,{"Column1.2"})
in
    #"Replaced Value1"
 

GraH - Guido

Well-Known Member
What about
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddCleanTxt = Table.AddColumn(Source, "Clean Text", each Text.Select([Column1],List.Combine({{"a".."z"}, {"A".."Z"},{"0".."9"}, {" "}}))),
    AddGetNumbers = Table.AddColumn(AddCleanTxt, "Get Numbers", each Text.Select([Clean Text],{"0".."9"}), type text),
    AddTxtAsList = Table.AddColumn(AddGetNumbers, "Text As List", each Text.Split([Clean Text], " ")),
    AddPositionOfNumber = Table.AddColumn(AddTxtAsList, "Position Of Number", each List.PositionOf([Text As List],[Get Numbers]) , Int64.Type),
    AddPositionOfNumber2 = Table.AddColumn(AddPositionOfNumber, "Position Of Number2", each if [Position Of Number] < 0 then
List.PositionOf(List.Transform([Text As List], each Text.Select(_,{"0".."9"})),[Get Numbers])
else [Position Of Number]+1),
    GetMeasure = Table.AddColumn(AddPositionOfNumber2, "Measure", each List.Transform(
    List.Range([Text As List],[Position Of Number2],1),
    each Text.Select(_,List.Combine({{"a".."z"},{"A".."Z"}})))),
    #"Expanded Measure" = Table.ExpandListColumn(GetMeasure, "Measure"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Measure",{"Text As List", "Position Of Number", "Position Of Number2", "Clean Text"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Get Numbers", "Measure"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Quantity")
in
    #"Merged Columns"
 

Attachments

GraH - Guido

Well-Known Member
To be completely in line with the requirement
eg:
Assorted Snack/Bars - 210 GMS (pick only 210 and GMS in seperate columns)
Skin Face Serum - 30 ml (pick only 30 and ml in seperate columns)
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AsTxt = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    AddCleanTxt = Table.AddColumn(AsTxt, "Clean Text", each Text.Select([Column1],List.Combine({{"a".."z"}, {"A".."Z"},{"0".."9"}, {" "}}))),
    AddGetNumbers = Table.AddColumn(AddCleanTxt, "Quantity", each Text.Select([Clean Text],{"0".."9"}), type text),
    AddTxtAsList = Table.AddColumn(AddGetNumbers, "Text As List", each Text.Split([Clean Text], " ")),
    AddPositionOfNumber = Table.AddColumn(AddTxtAsList, "Position Of Number", each List.PositionOf([Text As List],[Quantity]) , Int64.Type),
    AddPositionOfNumber2 = Table.AddColumn(AddPositionOfNumber, "Position Of Number2", each if [Position Of Number] < 0 then
List.PositionOf(List.Transform([Text As List], each Text.Select(_,{"0".."9"})),[Quantity])
else [Position Of Number]+1),
    GetMeasure = Table.AddColumn(AddPositionOfNumber2, "Measure", each List.Transform(
    List.Range([Text As List],[Position Of Number2],1),
    each Text.Select(_,List.Combine({{"a".."z"},{"A".."Z"}})))),
    ExpandMeasure = Table.ExpandListColumn(GetMeasure, "Measure"),
    RemoveHelperCols = Table.RemoveColumns(ExpandMeasure,{"Text As List", "Position Of Number", "Position Of Number2", "Clean Text"}),
    ChangeTypes = Table.TransformColumnTypes(RemoveHelperCols,{{"Quantity", Int64.Type}, {"Measure", type text}})
in
    ChangeTypes
 

Attachments

Brunda

Member
Because you don't have consistent delimeters or layout, the following is the best I can do for you. Hopefully, someone with more skill set can assist here

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition","}","",Replacer.ReplaceText,{"Column1.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",")","",Replacer.ReplaceText,{"Column1.2"})
in
    #"Replaced Value1"
Thanks a lot
 

Brunda

Member
To be completely in line with the requirement

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AsTxt = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    AddCleanTxt = Table.AddColumn(AsTxt, "Clean Text", each Text.Select([Column1],List.Combine({{"a".."z"}, {"A".."Z"},{"0".."9"}, {" "}}))),
    AddGetNumbers = Table.AddColumn(AddCleanTxt, "Quantity", each Text.Select([Clean Text],{"0".."9"}), type text),
    AddTxtAsList = Table.AddColumn(AddGetNumbers, "Text As List", each Text.Split([Clean Text], " ")),
    AddPositionOfNumber = Table.AddColumn(AddTxtAsList, "Position Of Number", each List.PositionOf([Text As List],[Quantity]) , Int64.Type),
    AddPositionOfNumber2 = Table.AddColumn(AddPositionOfNumber, "Position Of Number2", each if [Position Of Number] < 0 then
List.PositionOf(List.Transform([Text As List], each Text.Select(_,{"0".."9"})),[Quantity])
else [Position Of Number]+1),
    GetMeasure = Table.AddColumn(AddPositionOfNumber2, "Measure", each List.Transform(
    List.Range([Text As List],[Position Of Number2],1),
    each Text.Select(_,List.Combine({{"a".."z"},{"A".."Z"}})))),
    ExpandMeasure = Table.ExpandListColumn(GetMeasure, "Measure"),
    RemoveHelperCols = Table.RemoveColumns(ExpandMeasure,{"Text As List", "Position Of Number", "Position Of Number2", "Clean Text"}),
    ChangeTypes = Table.TransformColumnTypes(RemoveHelperCols,{{"Quantity", Int64.Type}, {"Measure", type text}})
in
    ChangeTypes
Hello,

Let me try this and Let you know how it works out for me

Thanks
 

Brunda

Member
To be completely in line with the requirement

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AsTxt = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    AddCleanTxt = Table.AddColumn(AsTxt, "Clean Text", each Text.Select([Column1],List.Combine({{"a".."z"}, {"A".."Z"},{"0".."9"}, {" "}}))),
    AddGetNumbers = Table.AddColumn(AddCleanTxt, "Quantity", each Text.Select([Clean Text],{"0".."9"}), type text),
    AddTxtAsList = Table.AddColumn(AddGetNumbers, "Text As List", each Text.Split([Clean Text], " ")),
    AddPositionOfNumber = Table.AddColumn(AddTxtAsList, "Position Of Number", each List.PositionOf([Text As List],[Quantity]) , Int64.Type),
    AddPositionOfNumber2 = Table.AddColumn(AddPositionOfNumber, "Position Of Number2", each if [Position Of Number] < 0 then
List.PositionOf(List.Transform([Text As List], each Text.Select(_,{"0".."9"})),[Quantity])
else [Position Of Number]+1),
    GetMeasure = Table.AddColumn(AddPositionOfNumber2, "Measure", each List.Transform(
    List.Range([Text As List],[Position Of Number2],1),
    each Text.Select(_,List.Combine({{"a".."z"},{"A".."Z"}})))),
    ExpandMeasure = Table.ExpandListColumn(GetMeasure, "Measure"),
    RemoveHelperCols = Table.RemoveColumns(ExpandMeasure,{"Text As List", "Position Of Number", "Position Of Number2", "Clean Text"}),
    ChangeTypes = Table.TransformColumnTypes(RemoveHelperCols,{{"Quantity", Int64.Type}, {"Measure", type text}})
in
    ChangeTypes
hello,

I just tested it, its works good its giving correct output but i need a little changes in that
those changes i have mentioned in the sheet 1
 

Attachments

GraH - Guido

Well-Known Member
Hi Brunda , that's another pattern, I was already afraid this could happen.

You have also the option to find the first number and get the measure for that number.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddCleanTxt = Table.AddColumn(Source, "Clean Text", each Text.Select([Column1],List.Combine({{"a".."z"}, {"A".."Z"},{"0".."9"}, {" "}}))),
    AddGetNumbers = Table.AddColumn(AddCleanTxt, "Get Numbers", each Text.Select([Clean Text],{"0".."9", " "}), type text),
    AddFirstNumber = Table.AddColumn(AddGetNumbers, "FirstNumber", each List.First(List.Select(Text.Split([Get Numbers], " "), each _ <> ""))),
    AddMultipleCheck = Table.AddColumn(AddFirstNumber, "Multiple?", each List.Count(List.Select(Text.Split([Get Numbers], " "), each _ <> ""))>1),
    AddTxtAsList = Table.AddColumn(AddMultipleCheck, "Text As List", each Text.Split([Clean Text], " ")),
    AddPositionOfNumber = Table.AddColumn(AddTxtAsList, "Position Of Number", each List.PositionOf([Text As List],[FirstNumber]) , Int64.Type),
    AddPositionOfNumber2 = Table.AddColumn(AddPositionOfNumber, "Position Of Number2", each if [Position Of Number] < 0 then
List.PositionOf(List.Transform([Text As List], each Text.Select(_,{"0".."9"})),[FirstNumber])
else [Position Of Number]+1),
    GetMeasure = Table.AddColumn(AddPositionOfNumber2, "Measure", each List.Transform(
    List.Range([Text As List],[Position Of Number2],1),
    each Text.Select(_,List.Combine({{"a".."z"},{"A".."Z"}})))),
    #"Expanded Measure" = Table.ExpandListColumn(GetMeasure, "Measure"),
    #"Final Quantity" = Table.AddColumn(#"Expanded Measure", "Quantity", each if [#"Multiple?"]=true then "NA" else [FirstNumber]& " "&[Measure]),
    #"Removed Columns" = Table.RemoveColumns(#"Final Quantity",{"Text As List", "Position Of Number", "Position Of Number2", "Clean Text", "Get Numbers", "Multiple?"})
in
    #"Removed Columns"
 

Attachments

Brunda

Member
Hi Brunda , that's another pattern, I was already afraid this could happen.

You have also the option to find the first number and get the measure for that number.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddCleanTxt = Table.AddColumn(Source, "Clean Text", each Text.Select([Column1],List.Combine({{"a".."z"}, {"A".."Z"},{"0".."9"}, {" "}}))),
    AddGetNumbers = Table.AddColumn(AddCleanTxt, "Get Numbers", each Text.Select([Clean Text],{"0".."9", " "}), type text),
    AddFirstNumber = Table.AddColumn(AddGetNumbers, "FirstNumber", each List.First(List.Select(Text.Split([Get Numbers], " "), each _ <> ""))),
    AddMultipleCheck = Table.AddColumn(AddFirstNumber, "Multiple?", each List.Count(List.Select(Text.Split([Get Numbers], " "), each _ <> ""))>1),
    AddTxtAsList = Table.AddColumn(AddMultipleCheck, "Text As List", each Text.Split([Clean Text], " ")),
    AddPositionOfNumber = Table.AddColumn(AddTxtAsList, "Position Of Number", each List.PositionOf([Text As List],[FirstNumber]) , Int64.Type),
    AddPositionOfNumber2 = Table.AddColumn(AddPositionOfNumber, "Position Of Number2", each if [Position Of Number] < 0 then
List.PositionOf(List.Transform([Text As List], each Text.Select(_,{"0".."9"})),[FirstNumber])
else [Position Of Number]+1),
    GetMeasure = Table.AddColumn(AddPositionOfNumber2, "Measure", each List.Transform(
    List.Range([Text As List],[Position Of Number2],1),
    each Text.Select(_,List.Combine({{"a".."z"},{"A".."Z"}})))),
    #"Expanded Measure" = Table.ExpandListColumn(GetMeasure, "Measure"),
    #"Final Quantity" = Table.AddColumn(#"Expanded Measure", "Quantity", each if [#"Multiple?"]=true then "NA" else [FirstNumber]& " "&[Measure]),
    #"Removed Columns" = Table.RemoveColumns(#"Final Quantity",{"Text As List", "Position Of Number", "Position Of Number2", "Clean Text", "Get Numbers", "Multiple?"})
in
    #"Removed Columns"
Hello,

This is perfectly working but if title with no number its not running so if there's no any quantity let it show NA


Thanks
 

Attachments

GraH - Guido

Well-Known Member
Again, a new pattern. Remember for future posts to ensure your sample data contains all scenarios.
New code:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddCleanTxt = Table.AddColumn(Source, "Clean Text", each Text.Select(Text.Replace([Column1],"-"," "),List.Combine({{"a".."z"}, {"A".."Z"},{"0".."9"}, {" "}}))),
    AddGetNumbers = Table.AddColumn(AddCleanTxt, "Get Numbers", each Text.Select([Clean Text],{"0".."9", " "}), type text),
    AddFirstNumber = Table.AddColumn(AddGetNumbers, "FirstNumber", each List.First(List.Select(Text.Split([Get Numbers], " "), each _ <> ""))),
    AddMultipleCheck = Table.AddColumn(AddFirstNumber, "Multiple?", each List.Count(List.Select(Text.Split([Get Numbers], " "), each _ <> ""))>1),
    AddTxtAsList = Table.AddColumn(AddMultipleCheck, "Text As List", each Text.Split([Clean Text], " ")),
    AddPositionOfNumber = Table.AddColumn(AddTxtAsList, "Position Of Number", each List.PositionOf([Text As List],[FirstNumber]) , Int64.Type),
    AddPositionOfNumber2 = Table.AddColumn(AddPositionOfNumber, "Position Of Number2", each if [Position Of Number] < 0 then
List.PositionOf(List.Transform([Text As List], each Text.Select(_,{"0".."9"})),[FirstNumber])
else [Position Of Number]+1),
    GetMeasure = Table.AddColumn(AddPositionOfNumber2, "Measure", each try List.Transform(
    List.Range([Text As List],[Position Of Number2],1),
    each Text.Select(_,List.Combine({{"a".."z"},{"A".."Z"}}))) otherwise null),
    #"Expanded Measure" = Table.ExpandListColumn(GetMeasure, "Measure"),
    #"Final Quantity" = Table.AddColumn(#"Expanded Measure", "Quantity", each if [#"Multiple?"]=true then "NA" else
if [Measure] = null then "NA"  else [FirstNumber]& " "&[Measure]),
    #"Removed Columns" = Table.RemoveColumns(#"Final Quantity",{"Text As List", "Position Of Number", "Position Of Number2", "Clean Text", "Get Numbers", "Multiple?"})
in
    #"Removed Columns"
 

Attachments

Brunda

Member
Again, a new pattern. Remember for future posts to ensure your sample data contains all scenarios.
New code:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddCleanTxt = Table.AddColumn(Source, "Clean Text", each Text.Select(Text.Replace([Column1],"-"," "),List.Combine({{"a".."z"}, {"A".."Z"},{"0".."9"}, {" "}}))),
    AddGetNumbers = Table.AddColumn(AddCleanTxt, "Get Numbers", each Text.Select([Clean Text],{"0".."9", " "}), type text),
    AddFirstNumber = Table.AddColumn(AddGetNumbers, "FirstNumber", each List.First(List.Select(Text.Split([Get Numbers], " "), each _ <> ""))),
    AddMultipleCheck = Table.AddColumn(AddFirstNumber, "Multiple?", each List.Count(List.Select(Text.Split([Get Numbers], " "), each _ <> ""))>1),
    AddTxtAsList = Table.AddColumn(AddMultipleCheck, "Text As List", each Text.Split([Clean Text], " ")),
    AddPositionOfNumber = Table.AddColumn(AddTxtAsList, "Position Of Number", each List.PositionOf([Text As List],[FirstNumber]) , Int64.Type),
    AddPositionOfNumber2 = Table.AddColumn(AddPositionOfNumber, "Position Of Number2", each if [Position Of Number] < 0 then
List.PositionOf(List.Transform([Text As List], each Text.Select(_,{"0".."9"})),[FirstNumber])
else [Position Of Number]+1),
    GetMeasure = Table.AddColumn(AddPositionOfNumber2, "Measure", each try List.Transform(
    List.Range([Text As List],[Position Of Number2],1),
    each Text.Select(_,List.Combine({{"a".."z"},{"A".."Z"}}))) otherwise null),
    #"Expanded Measure" = Table.ExpandListColumn(GetMeasure, "Measure"),
    #"Final Quantity" = Table.AddColumn(#"Expanded Measure", "Quantity", each if [#"Multiple?"]=true then "NA" else
if [Measure] = null then "NA"  else [FirstNumber]& " "&[Measure]),
    #"Removed Columns" = Table.RemoveColumns(#"Final Quantity",{"Text As List", "Position Of Number", "Position Of Number2", "Clean Text", "Get Numbers", "Multiple?"})
in
    #"Removed Columns"
Hello,

Yeah sure, i will check it and let you know by monday

Thanks
 

Brunda

Member
Again, a new pattern. Remember for future posts to ensure your sample data contains all scenarios.
New code:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddCleanTxt = Table.AddColumn(Source, "Clean Text", each Text.Select(Text.Replace([Column1],"-"," "),List.Combine({{"a".."z"}, {"A".."Z"},{"0".."9"}, {" "}}))),
    AddGetNumbers = Table.AddColumn(AddCleanTxt, "Get Numbers", each Text.Select([Clean Text],{"0".."9", " "}), type text),
    AddFirstNumber = Table.AddColumn(AddGetNumbers, "FirstNumber", each List.First(List.Select(Text.Split([Get Numbers], " "), each _ <> ""))),
    AddMultipleCheck = Table.AddColumn(AddFirstNumber, "Multiple?", each List.Count(List.Select(Text.Split([Get Numbers], " "), each _ <> ""))>1),
    AddTxtAsList = Table.AddColumn(AddMultipleCheck, "Text As List", each Text.Split([Clean Text], " ")),
    AddPositionOfNumber = Table.AddColumn(AddTxtAsList, "Position Of Number", each List.PositionOf([Text As List],[FirstNumber]) , Int64.Type),
    AddPositionOfNumber2 = Table.AddColumn(AddPositionOfNumber, "Position Of Number2", each if [Position Of Number] < 0 then
List.PositionOf(List.Transform([Text As List], each Text.Select(_,{"0".."9"})),[FirstNumber])
else [Position Of Number]+1),
    GetMeasure = Table.AddColumn(AddPositionOfNumber2, "Measure", each try List.Transform(
    List.Range([Text As List],[Position Of Number2],1),
    each Text.Select(_,List.Combine({{"a".."z"},{"A".."Z"}}))) otherwise null),
    #"Expanded Measure" = Table.ExpandListColumn(GetMeasure, "Measure"),
    #"Final Quantity" = Table.AddColumn(#"Expanded Measure", "Quantity", each if [#"Multiple?"]=true then "NA" else
if [Measure] = null then "NA"  else [FirstNumber]& " "&[Measure]),
    #"Removed Columns" = Table.RemoveColumns(#"Final Quantity",{"Text As List", "Position Of Number", "Position Of Number2", "Clean Text", "Get Numbers", "Multiple?"})
in
    #"Removed Columns"
Hello,

I have Mentioned all types of scenarios in the sheet.
Kindly check let me know if it could work out


Thanks
 

Attachments

GraH - Guido

Well-Known Member
And now it turned into a challenge...

Looking at the data is seems some kind of export (typo's found due to wrong "File Origin" settings, is what I suspect) So the logical question is: those quantities and metrics aren't they available as such in the source data?

Can't promise I can make this one work.
 

Brunda

Member
And now it turned into a challenge...

Looking at the data is seems some kind of export (typo's found due to wrong "File Origin" settings, is what I suspect) So the logical question is: those quantities and metrics aren't they available as such in the source data?

Can't promise I can make this one work.
Hello,

Its a title exported and use those title to mark quantity and sometimes source data might to be updated wrong
is it possible to fetch on point values. consider only the title mark in red in sheet 1 and rest everything would be fine will try to manage with how much it fetchs it works good for us

Thanks
 

Attachments

GraH - Guido

Well-Known Member
Hi again,

I had to go with an all other tactical plan here. Had to use table functions to get there.
3% of your data turns out without a valid result (so its NA).

The file turned out too large to upload, so I delete many records.

Here is the query
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Titles"]}[Content],
    Measures = List.Buffer({"kg","g","gram","gm","ml","gms","grams","lbs","lb"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}}),
    AddCleanTitle = Table.AddColumn(#"Changed Type", "Clean Title", each Text.Remove([Title], {"{","}","[","]","(",")","-", ",","/"})),
    AddTitleAsTable = Table.AddColumn(AddCleanTitle, "TitleAsTable", each Table.FromList(
    Text.Split([Clean Title], " ")
    , null
    , {"Title"}
)),
    AddIndex = Table.AddColumn(AddTitleAsTable, "Indexed", each Table.AddIndexColumn(Table.AddIndexColumn([TitleAsTable],"Index", 0,1),"Index2",1,1)),
    GetQuantity = Table.AddColumn(AddIndex, "SelectQuantity", each Table.AddColumn([Indexed],"HasNumber", each
 if Text.Select([Title],{"0".."9"}) <> ""
 then [Title]
 else null
 
 )),
    GetMeasure = Table.AddColumn(GetQuantity, "HasMeasure", each Table.AddColumn([SelectQuantity],"HasMeasure",each List.ContainsAny({[Title]},
Measures , Comparer.OrdinalIgnoreCase)
or Text.Contains([HasNumber], "g", Comparer.OrdinalIgnoreCase)
or Text.Contains([HasNumber], "l", Comparer.OrdinalIgnoreCase)
)),
    FilterTable = Table.AddColumn(GetMeasure, "Filter", each Table.SelectRows([HasMeasure], each [HasNumber] <> null or [HasMeasure] = true)),
    MergeTableOnTable = Table.AddColumn(FilterTable, "MergeTableOnTable", each Table.NestedJoin([Filter], {"Index2"}, [Filter], {"Index"}, "Filter", JoinKind.LeftOuter)),
    AddExpanded = Table.AddColumn(MergeTableOnTable, "Expanded", each Table.ExpandTableColumn([MergeTableOnTable], "Filter", {"Title"}, {"Measure"})),
    SortTable = Table.AddColumn(AddExpanded, "SortedTable", each Table.Sort([Expanded],{"Index", Order.Ascending})),
    FinalRecord = Table.AddColumn(SortTable, "FinalTable", each try Table.SelectRows([SortedTable], each [HasMeasure] = true or [Measure] <> null){0} otherwise null),
    ExpandRecord = Table.ExpandRecordColumn(FinalRecord, "FinalTable", {"HasNumber", "Measure"}, {"HasNumber", "Measure"}),
    GetMetric = Table.AddColumn(ExpandRecord, "Metric", each if [Measure] <> null then [Measure] else Text.Select([HasNumber], List.Combine({{"a".."z"},{"A","Z"}}))),
    FinalQuantity = Table.AddColumn(GetMetric, "Quantity", each if [Measure] <> null then [HasNumber] else Text.Select([HasNumber], {"0".."9"})),
    #"Removed Other Columns" = Table.SelectColumns(FinalQuantity,{"Title", "Quantity", "Metric"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,"NA",Replacer.ReplaceValue,{"Quantity","Metric"})
in
    #"Replaced Value"
 

Attachments

Brunda

Member
Hi again,

I had to go with an all other tactical plan here. Had to use table functions to get there.
3% of your data turns out without a valid result (so its NA).

The file turned out too large to upload, so I delete many records.

Here is the query
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Titles"]}[Content],
    Measures = List.Buffer({"kg","g","gram","gm","ml","gms","grams","lbs","lb"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}}),
    AddCleanTitle = Table.AddColumn(#"Changed Type", "Clean Title", each Text.Remove([Title], {"{","}","[","]","(",")","-", ",","/"})),
    AddTitleAsTable = Table.AddColumn(AddCleanTitle, "TitleAsTable", each Table.FromList(
    Text.Split([Clean Title], " ")
    , null
    , {"Title"}
)),
    AddIndex = Table.AddColumn(AddTitleAsTable, "Indexed", each Table.AddIndexColumn(Table.AddIndexColumn([TitleAsTable],"Index", 0,1),"Index2",1,1)),
    GetQuantity = Table.AddColumn(AddIndex, "SelectQuantity", each Table.AddColumn([Indexed],"HasNumber", each
if Text.Select([Title],{"0".."9"}) <> ""
then [Title]
else null

)),
    GetMeasure = Table.AddColumn(GetQuantity, "HasMeasure", each Table.AddColumn([SelectQuantity],"HasMeasure",each List.ContainsAny({[Title]},
Measures , Comparer.OrdinalIgnoreCase)
or Text.Contains([HasNumber], "g", Comparer.OrdinalIgnoreCase)
or Text.Contains([HasNumber], "l", Comparer.OrdinalIgnoreCase)
)),
    FilterTable = Table.AddColumn(GetMeasure, "Filter", each Table.SelectRows([HasMeasure], each [HasNumber] <> null or [HasMeasure] = true)),
    MergeTableOnTable = Table.AddColumn(FilterTable, "MergeTableOnTable", each Table.NestedJoin([Filter], {"Index2"}, [Filter], {"Index"}, "Filter", JoinKind.LeftOuter)),
    AddExpanded = Table.AddColumn(MergeTableOnTable, "Expanded", each Table.ExpandTableColumn([MergeTableOnTable], "Filter", {"Title"}, {"Measure"})),
    SortTable = Table.AddColumn(AddExpanded, "SortedTable", each Table.Sort([Expanded],{"Index", Order.Ascending})),
    FinalRecord = Table.AddColumn(SortTable, "FinalTable", each try Table.SelectRows([SortedTable], each [HasMeasure] = true or [Measure] <> null){0} otherwise null),
    ExpandRecord = Table.ExpandRecordColumn(FinalRecord, "FinalTable", {"HasNumber", "Measure"}, {"HasNumber", "Measure"}),
    GetMetric = Table.AddColumn(ExpandRecord, "Metric", each if [Measure] <> null then [Measure] else Text.Select([HasNumber], List.Combine({{"a".."z"},{"A","Z"}}))),
    FinalQuantity = Table.AddColumn(GetMetric, "Quantity", each if [Measure] <> null then [HasNumber] else Text.Select([HasNumber], {"0".."9"})),
    #"Removed Other Columns" = Table.SelectColumns(FinalQuantity,{"Title", "Quantity", "Metric"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,"NA",Replacer.ReplaceValue,{"Quantity","Metric"})
in
    #"Replaced Value"
Hello,

Previous whatever you did it was prefect where it was showing quantity metric and both together also at last for value present in title
for packs and set it was showing quantity metric and NA at last column with was easy for us to filter out those items
now requirement is:
to get the decimal value present as quantity like eg: 3.4 kg
output needed inform of title, quantity, metric and quantity&metrics in one column like previous sheet
and the last column with NA for packs & sets in the title everything else is perfectly working which you did pervious sheet had and this sheet its not showing that form
 

GraH - Guido

Well-Known Member
Last attempt, what about this custom function?
Code:
let GetMeasures = (Text as text) as any =>
    
    let
          NoCurly = Text.Replace(Text, "}"," *** ")
        , NoDash = Text.Replace(NoCurly, "-"," *** ")
        , NoComma = Text.Replace(NoDash, ","," *** ")
        , AddSpacePct = Text.Replace(NoComma, "%","% ")
        , AddSpace1 = Text.Replace(AddSpacePct, "("," ( ")
        , AddSpace2 = Text.Replace(AddSpace1, ")"," ) ")
        , LowCase = Text.Lower(AddSpace2)
        , StdKg1 = Text.Replace(LowCase, " kg ","|kg    ")
        , StdKg2 = Text.Replace(StdKg1, "kg ","|kg    ")
        , StdKg = Text.Replace(StdKg2, " |kg ","|kg    ")
        , StdMl1 = Text.Replace(StdKg, " ml ","|ml    ")
        , StdMl2 = Text.Replace(StdMl1, "ml ","|ml    ")
        , StdMl = Text.Replace(StdMl2, " |ml ","|ml    ")
        , Stdgm1 = Text.Replace(StdMl, " gm ","|g    ")
        , Stdgm2 = Text.Replace(Stdgm1, "gm ","|g    ")
        , Stdgm = Text.Replace(Stdgm2, "  |gm ","|g    ")
        , Stdgram1 = Text.Replace(Stdgm, " gram","|g    ")
        , Stdgram2 = Text.Replace(Stdgram1, "gram","|g    ")
        , Stdgram = Text.Replace(Stdgram2, " |gram","|g    ")
        , Stdg1 = Text.Replace(Stdgram, " g ","|g    ")
        , Stdg2 = Text.Replace(Stdg1, "g ","|g    ")
        , Stdg = Text.Replace(Stdg2, " |g ","|g    ")
        , Stdlb1 = Text.Replace(Stdg, " lb ","|lb   ")
        , Stdlb2 = Text.Replace(Stdlb1, "lb ","|lb   ")
        , Stdlb = Text.Replace(Stdlb2, " |lb","|lb   ")
        , NormPipes = Text.Replace(Stdlb, "||","|")
        , NormPipeG = Text.Replace(NormPipes, "| g","|g ")
        , NormPipesKG = Text.Replace(NormPipeG, "|k|g","|kg ")
        , AsList = Text.Split(NormPipesKG, " ")
        , SelectNmbrs = List.Select(AsList, each Text.Select(_,{"0".."9"}) <> "" and Text.Select(_,{"|"}) <> ""  )
        , Counter = List.Count(SelectNmbrs)
        , MakeRecord = [ Quantity = if Counter = 0 or Counter > 1
                                    then "NA"
                                    else Text.Select(SelectNmbrs{0}, {"0".."9", "."})
                        , Measure = if Quantity = "NA" then "NA"
                                   else Text.Select(SelectNmbrs{0}, {"a".."z"})
                        , #"Quantity-Measure" = if Quantity = "NA" then "NA" else Quantity& " " &Measure
                        , #"Multiple Measures" = if Counter > 1
                                            then Text.Replace(Text.Combine(SelectNmbrs, " , "), "|"," ")
                                            else "NA"
                        ]
    in
        MakeRecord

in
    GetMeasures
 

Attachments

Brunda

Member
Last attempt, what about this custom function?
Code:
let GetMeasures = (Text as text) as any =>
   
    let
          NoCurly = Text.Replace(Text, "}"," *** ")
        , NoDash = Text.Replace(NoCurly, "-"," *** ")
        , NoComma = Text.Replace(NoDash, ","," *** ")
        , AddSpacePct = Text.Replace(NoComma, "%","% ")
        , AddSpace1 = Text.Replace(AddSpacePct, "("," ( ")
        , AddSpace2 = Text.Replace(AddSpace1, ")"," ) ")
        , LowCase = Text.Lower(AddSpace2)
        , StdKg1 = Text.Replace(LowCase, " kg ","|kg    ")
        , StdKg2 = Text.Replace(StdKg1, "kg ","|kg    ")
        , StdKg = Text.Replace(StdKg2, " |kg ","|kg    ")
        , StdMl1 = Text.Replace(StdKg, " ml ","|ml    ")
        , StdMl2 = Text.Replace(StdMl1, "ml ","|ml    ")
        , StdMl = Text.Replace(StdMl2, " |ml ","|ml    ")
        , Stdgm1 = Text.Replace(StdMl, " gm ","|g    ")
        , Stdgm2 = Text.Replace(Stdgm1, "gm ","|g    ")
        , Stdgm = Text.Replace(Stdgm2, "  |gm ","|g    ")
        , Stdgram1 = Text.Replace(Stdgm, " gram","|g    ")
        , Stdgram2 = Text.Replace(Stdgram1, "gram","|g    ")
        , Stdgram = Text.Replace(Stdgram2, " |gram","|g    ")
        , Stdg1 = Text.Replace(Stdgram, " g ","|g    ")
        , Stdg2 = Text.Replace(Stdg1, "g ","|g    ")
        , Stdg = Text.Replace(Stdg2, " |g ","|g    ")
        , Stdlb1 = Text.Replace(Stdg, " lb ","|lb   ")
        , Stdlb2 = Text.Replace(Stdlb1, "lb ","|lb   ")
        , Stdlb = Text.Replace(Stdlb2, " |lb","|lb   ")
        , NormPipes = Text.Replace(Stdlb, "||","|")
        , NormPipeG = Text.Replace(NormPipes, "| g","|g ")
        , NormPipesKG = Text.Replace(NormPipeG, "|k|g","|kg ")
        , AsList = Text.Split(NormPipesKG, " ")
        , SelectNmbrs = List.Select(AsList, each Text.Select(_,{"0".."9"}) <> "" and Text.Select(_,{"|"}) <> ""  )
        , Counter = List.Count(SelectNmbrs)
        , MakeRecord = [ Quantity = if Counter = 0 or Counter > 1
                                    then "NA"
                                    else Text.Select(SelectNmbrs{0}, {"0".."9", "."})
                        , Measure = if Quantity = "NA" then "NA"
                                   else Text.Select(SelectNmbrs{0}, {"a".."z"})
                        , #"Quantity-Measure" = if Quantity = "NA" then "NA" else Quantity& " " &Measure
                        , #"Multiple Measures" = if Counter > 1
                                            then Text.Replace(Text.Combine(SelectNmbrs, " , "), "|"," ")
                                            else "NA"
                        ]
    in
        MakeRecord

in
    GetMeasures
Hello,

This is not working fine for the the title having only quantity
 

Attachments

Brunda

Member
Last attempt, what about this custom function?
Code:
let GetMeasures = (Text as text) as any =>
   
    let
          NoCurly = Text.Replace(Text, "}"," *** ")
        , NoDash = Text.Replace(NoCurly, "-"," *** ")
        , NoComma = Text.Replace(NoDash, ","," *** ")
        , AddSpacePct = Text.Replace(NoComma, "%","% ")
        , AddSpace1 = Text.Replace(AddSpacePct, "("," ( ")
        , AddSpace2 = Text.Replace(AddSpace1, ")"," ) ")
        , LowCase = Text.Lower(AddSpace2)
        , StdKg1 = Text.Replace(LowCase, " kg ","|kg    ")
        , StdKg2 = Text.Replace(StdKg1, "kg ","|kg    ")
        , StdKg = Text.Replace(StdKg2, " |kg ","|kg    ")
        , StdMl1 = Text.Replace(StdKg, " ml ","|ml    ")
        , StdMl2 = Text.Replace(StdMl1, "ml ","|ml    ")
        , StdMl = Text.Replace(StdMl2, " |ml ","|ml    ")
        , Stdgm1 = Text.Replace(StdMl, " gm ","|g    ")
        , Stdgm2 = Text.Replace(Stdgm1, "gm ","|g    ")
        , Stdgm = Text.Replace(Stdgm2, "  |gm ","|g    ")
        , Stdgram1 = Text.Replace(Stdgm, " gram","|g    ")
        , Stdgram2 = Text.Replace(Stdgram1, "gram","|g    ")
        , Stdgram = Text.Replace(Stdgram2, " |gram","|g    ")
        , Stdg1 = Text.Replace(Stdgram, " g ","|g    ")
        , Stdg2 = Text.Replace(Stdg1, "g ","|g    ")
        , Stdg = Text.Replace(Stdg2, " |g ","|g    ")
        , Stdlb1 = Text.Replace(Stdg, " lb ","|lb   ")
        , Stdlb2 = Text.Replace(Stdlb1, "lb ","|lb   ")
        , Stdlb = Text.Replace(Stdlb2, " |lb","|lb   ")
        , NormPipes = Text.Replace(Stdlb, "||","|")
        , NormPipeG = Text.Replace(NormPipes, "| g","|g ")
        , NormPipesKG = Text.Replace(NormPipeG, "|k|g","|kg ")
        , AsList = Text.Split(NormPipesKG, " ")
        , SelectNmbrs = List.Select(AsList, each Text.Select(_,{"0".."9"}) <> "" and Text.Select(_,{"|"}) <> ""  )
        , Counter = List.Count(SelectNmbrs)
        , MakeRecord = [ Quantity = if Counter = 0 or Counter > 1
                                    then "NA"
                                    else Text.Select(SelectNmbrs{0}, {"0".."9", "."})
                        , Measure = if Quantity = "NA" then "NA"
                                   else Text.Select(SelectNmbrs{0}, {"a".."z"})
                        , #"Quantity-Measure" = if Quantity = "NA" then "NA" else Quantity& " " &Measure
                        , #"Multiple Measures" = if Counter > 1
                                            then Text.Replace(Text.Combine(SelectNmbrs, " , "), "|"," ")
                                            else "NA"
                        ]
    in
        MakeRecord

in
    GetMeasures
Hello,

Let the macro be in the previous way only it works best for us
i just i need it to fetch the decimal values thats it
kindly help with that other than that i dont need any changes

Thanks.
 

Attachments

Brunda

Member
Small tweak, but I discover 2 new scenarios, which are not covered:
- litre/L
- o.z.

I don't understand "i just i need it to fetch the decimal values thats it".
Hello,

this is not so efficient one for our usage for some its giving value some its not
the sheet attached here was working good for us just needed thing in it should fetch
eg: 3.2ml as to be fetched as 3.2 ml
Kindly help us to get those point values as well

Thanks
 

Attachments

Brunda

Member
Small tweak, but I discover 2 new scenarios, which are not covered:
- litre/L
- o.z.

I don't understand "i just i need it to fetch the decimal values thats it".
I just tested this query which is considering space and not giving the value

eg: for no space - 4g - for this its giving NA
for double space - 30 ml - for this its giving NA
for single space - 6 ml - for the only value its giving output correctly rest all is NA

In title it will have for
grams - g,gm,grms,grams,Gm,G,Grams,Kg,kg,kilogram,Kilograms
ml - ml,millilitre,L,Litres,Litre,Litres,litres,litre
oz, o.z., ounce
fl oz, fl.oz, fluid ounce
 

Attachments

Brunda

Member
Kindly consider the both file attached above and provide us the both the sheet i have provided all the nessecary things needed for those
one attachement shared at 1:51 AM IST
one attachement shared now few mins ago
Please do help us with this its a much needed one

Thanks
 
Top