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,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"
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"
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"
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)
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
Thanks a lotBecause 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"
Hello,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,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
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"
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"
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,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,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,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.
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,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"
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,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,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,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 valueSmall 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".