Trim specific numbers from a list of text and numbers

[Anonymous]

I have a column of text and numbers, from which I want to extract the tonnage of each machine. For most of these, these are the 3-4 numbers before the word "ton" but several of them leave out "ton" entirely. 

I've used this line to create a new column:

= Table.AddColumn(#"Added Custom4", "Weight", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([CNTR_DESC]),each if Value.Is(Value.FromText(_), type number) then _ else null))))

but it's taking all numbers from the descriptions.

Here's a portion of the list:

650 H HUSKY

500TON VAN DORN/#2922/B=60oz/S=GP/R=SEPRO

132TON ZHAFIR ZERES/#18800/B=225oz/S=GP/R=N/A

176TON HAITIAN/#11960/B=8.11oz/S=GP/R=WITTMANN

176TON HAITIAN/#12140/B=8.11oz/S=GP/R=WITTMANN

140TON NISSEI/#10760/B=11.8oz/S=GP/R=N/A

550TON ARBURG/#11778/B=34.7oz/S=GP/R=SEPRO

180TON SUMITOMO/#9909/B=10.7oz/S=GP/R=WITTMANN

180TON NISSEI/#11205/B=14.97oz/S=GP/R=CONAIR

21A06 662 HAITIAN JU6500II ID 19345 (ID#019354-ROBOT)

550TON ARBURG/#11459/B=34.7oz/S=GP/R=SEPRO

500TON VAN DORN/#2922/B=60oz/S=GP/R=SEPRO

730TON HAITIAN/#19597/B=116.86/S=GP/R=WITTMANN

21C07 309 TON NISSEI FN-6000 (ID# 010137-ROBOT ID 13298-[1.36Lbs 21.71Oz] SCW GP 63mm)

180TON SUMITOMO/#9909/B=10.7oz/S=GP/R=WITTMANN

21B11 398 TON NISSEI FN-7000 (ID# 010251-ID ROBOT N/A [2.13Lbs 34.2Oz] SCW GP 71mm)

650 TON HUSKY

650 H HUSKY

300 VAN DORN

650 H HUSKY

500TON VANDORN/#2011/B=59.2oz/S=GP/R=SEPRO

21C11 180 TON HAITIAN MA1600-600A (ID# 012331-ROBOT ID 11277-[0.51Lbs 8.1Oz] SCW GP 40mm

21C11 180 TON HAITIAN MA1600-600A (ID# 012331-ROBOT ID 11277-[0.51Lbs 8.1Oz] SCW GP 40mm

21B02 400 TON V-D 400-HT-1920 (ID# 2534 Robot ID 14554 [2.52 Lbs  40.3 Oz]  SCW GP 70mm)

500 TON HUSKY

400TON VAN DORN/#4782/B=40.4oz/S=GP/R=SEPRO

21C11 180 TON HAITIAN MA1600-600A (ID# 012331-ROBOT ID 11277-[0.51Lbs 8.1Oz] SCW GP 40mm

550TON ARBURG/#11459/B=34.7oz/S=GP/R=SEPRO

HUSKY H4L600 RS95

Thanks!

[mussaenda]

Based on your data, try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vVM7b9swEP4rB3tpgEq6O77EwYP8iMU2lgxJTtAIXrIbHtopv74kHceyK7RIh2wk8H3H78Hr+4lWCCWUu/b7j8n+az9RiF1dwWNRwbJuqmzKljmbzzQeX7N2tt5mzaxdbZs6oklwQD+Xxb1r4HnVrNpsSnmO6CnM6sKpsuLEMDowysJ1rvDjiawO4DwluqCfXNdtiqoapzDJf1FkdFG5tl05z0ATHyFK8z8lKRXBRTPfNeugyJjcg4VMzajnPMLb3cZ19abOptaiDcNxiL+Wk1/LIUYVGDK1A8qirgrXRAJTgRq05rNr+LbzTaFz4JZAVkgFX9xyiv6oZNLU87q7Gzcjlf2LmY/VbQReN2GVNTFXneZ61DrTAg0ItHBJAO6rRCNitABISMKcPER3gm2e9JQK/fDyE5hSQ/XrHtrFE6y3oMXhcPe/RTDNiUDY/EaNGaphRYnXcRLkPwn0nJIIWnyKPJBi6CwlbFFM5n2PbvdK+AfOKY8CLk28FYFE3oyyKY9V4XP1TnwEMGgENgX5YEO4xdkOC0GDcImNSXpMFQVDfoUGfiQeDp8xfI7sr6fhj8kynJOyS8jyWwm**bleep**m+HL8FcdKpWQoQTGEuR6fCgij34vAcxEKb4uQtz9cmjz8cD9EfnqwH9zO6AJK+eDfhaa1arLf/wY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Column1 - Copy", each Text.BeforeDelimiter(_, "TON"), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text Before Delimiter", "Column1 - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.None), {"Column1 - Copy.1", "Column1 - Copy.2", "Column1 - Copy.3", "Column1 - Copy.4", "Column1 - Copy.5", "Column1 - Copy.6", "Column1 - Copy.7", "Column1 - Copy.8", "Column1 - Copy.9", "Column1 - Copy.10", "Column1 - Copy.11", "Column1 - Copy.12", "Column1 - Copy.13", "Column1 - Copy.14", "Column1 - Copy.15"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1 - Copy.1", type text}, {"Column1 - Copy.2", type text}, {"Column1 - Copy.3", type text}, {"Column1 - Copy.4", type text}, {"Column1 - Copy.5", type text}, {"Column1 - Copy.6", Int64.Type}, {"Column1 - Copy.7", type text}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type1", "Column1 - Copy.1", "Column1 - Copy.1 - Copy"),
    #"Calculated Text Length" = Table.TransformColumns(#"Duplicated Column1",{{"Column1 - Copy.1 - Copy", Text.Length, Int64.Type}}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Calculated Text Length", "Column1 - Copy.2", "Column1 - Copy.2 - Copy"),
    #"Calculated Text Length1" = Table.TransformColumns(#"Duplicated Column2",{{"Column1 - Copy.2 - Copy", Text.Length, Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Calculated Text Length1", "TONNAGE", each if[#"Column1 - Copy.1 - Copy"] <= 4 and [#"Column1 - Copy.1 - Copy"] >= 3 then [#"Column1 - Copy.1"] else 

if [#"Column1 - Copy.2 - Copy"] <=4 and [#"Column1 - Copy.2 - Copy"] >=3 then [#"Column1 - Copy.2"] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1 - Copy.1", "Column1 - Copy.2", "Column1 - Copy.3", "Column1 - Copy.4", "Column1 - Copy.5", "Column1 - Copy.6", "Column1 - Copy.7", "Column1 - Copy.8", "Column1 - Copy.9", "Column1 - Copy.10", "Column1 - Copy.11", "Column1 - Copy.12", "Column1 - Copy.13", "Column1 - Copy.14", "Column1 - Copy.15", "Column1 - Copy.1 - Copy", "Column1 - Copy.2 - Copy"})
in
    #"Removed Columns"

[Anonymous]

Thanks @mussaenda,

This looks good, is there anyway to trim the 600 out of the value from row 29? As seen there, some of the tonnages are built into the naming convention.

[mussaenda]

Can you give me samples of tonnages in naming convention aside from row 29 just to see the pattern to determine the steps to apply

[Anonymous]

@mussaenda,

Sure thing. I used this method and it worked for the most part but there's blanks/errors. Here's the full list.

[mussaenda]

Hi @Anonymous ,

Thanks ffor providing a full list.

I checked it and found some strings with these:

which is not stated on your requirement.

Maybe @ImkeF  or @MarcelBeug who are experts in power query might help you

[Anonymous]

Got it, thanks for taking a shot at it. A more standardized method of describing the machines would make everything a lot easier.

[parry2k]

@Anonymous do you want value before TON , correct?

[Anonymous]

Hi @parry2k ,

Yes that's where the value is for the most part, but some of the names either leave out the word TON or have the numbers for relating to tonnage built into another number (ex. row 29).