Text To Columns - GR

Hi Lwpro,

it is not an easy task because we don't see exact data, but something like this could help.

Edit 2024-03-01: I've updated my code a bit

It is mandantory to have space after each unit of measure (or it has to be at the end of text string)

Result:

You have to define units of measure in 2nd step (just separate them with coma)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY+xCsIwFEV/5VJwc3htreioDgGhKDg4lA6ljWkgTSRNBP/epA62mPHcw819VZWcetMa1TiOlGgFYEcsqddVcu9lgL94T2LiRzm2XjpsyfU4aC1f3I6NfSMQgfiYUd2kXq3pfOvwsGaI9QLOoKC/IgjcpAo9E59/CbZQ04wYMso2OHvNF1HpBUrjwhoUeRwRvfzwtFJ9zwmFNo4ABoWLbbTgyyDgMC8taC7UHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t]),
    DefineUnitsOfMeasureHere = "g, kg, ml, l",
    Ad_Weight = Table.AddColumn(Source, "Weight", each [ l_units = List.Buffer(List.Transform(Text.Split(DefineUnitsOfMeasureHere, ","), Text.Trim)),
       l_removedSpaces = List.ReplaceMatchingItems(List.RemoveItems(Text.Split(Text.Upper([Item]), " "), {""}), List.Zip( { List.Transform(l_units, Text.Upper), l_units } )),
       t_replaceUnitsSpaces = 
           List.Accumulate(
              List.Zip( { List.Transform(l_units, each " " & _), l_units } ),
              Text.Combine(l_removedSpaces, " "),
              (s,c)=> Text.Replace(s, c{0}, c{1} )
           ),
       //Transform to lower text list and remove rows with numbers only    
       l_weight1 = List.Select(List.Transform(Text.Split(t_replaceUnitsSpaces, " "), Text.Lower), each (try Number.From(_) otherwise _) is text),
       
       //Select only valid weight
       l_weight2 =
            List.Accumulate(
                l_units,
                {},
                (s,c)=> s & List.Transform( l_weight1, each 
                                if Text.EndsWith(_, c)
                                and (try Number.From(Text.Replace(_, c, "")) otherwise _) is number
                                then _ else null )
            ),
       l_weightFinal = List.RemoveNulls(l_weight2),      
       t_result = if List.Count(l_weight2) > 1 then Text.Combine(l_weight2, " to ") else l_weight2{0}
     ][t_result], type text)
in
    Ad_Weight

Hi Lwpro,

Alternatively, you can try something like this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7IT87PSSxJVVCwMHBXitWJVgrPyARyERKWBulgcafM4uTSzBIFBQUzg3QFEHDPz0kBSwUU5aeUJpcopBXl5yoYGgClS/IVTA3QNJoZKKQrBGfmlKUWgcWRrVAA2h0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t]),
    getWeight = Table.AddColumn(Source, "Weight", each 
        [
            a = List.RemoveMatchingItems( List.Transform( Splitter.SplitTextByCharacterTransition( each true, {"G", "g"})([Item]), (x)=> Text.Select(Text.Trim(x), {"0".."9"}) ), {"", null}),
            b = if List.Count(a) >1 then Text.Combine( a, "g to ") & "g" else List.First(a) & "g"
        ][b], type text
            
    )
in
    getWeight

That will get you this result.

or maybe even this, as there appear to be no other numbers contained within your string.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7IT87PSSxJVVCwMHBXitWJVgrPyARyERKWBulgcafM4uTSzBIFBQUzg3QFEHDPz0kBSwUU5aeUJpcopBXl5yoYGgClS/IVTA3QNJoZKKQrBGfmlKUWgcWRrVAA2h0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t]),
    getWeight = Table.AddColumn(Source, "Weight", each 
        [
            a = Text.Split([Item], " "),
            b = List.RemoveMatchingItems( List.Transform(a, each Text.Select(_, {"0".."9"})), {"", null}),
            c = if List.Count(b) >1 then Text.Combine( b, "g to ") & "g" else List.First(b) & "g"
        ][c], type text
            
    )
in
    getWeight

I hope this is helpful