Regex for subtracting texts by Power Query/ Dax

Hi ngct1112 ,

please paste this code into the advanced editor and follow the steps:

 

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45Wcs7PKc3NU9JRCkotLs0pUYrViVYyzE4HCoBIEC8xKdnQACJiABXC4FtkpytkZGSYgAQtoIKOSclW6ApNE7PTExOTjMGipggLFHQVDE0hSmGiEDXGUJ4lmGcJ5YWFATWkpStYgEVhFmZnKwA1KxgYIBtuYGD4qGGZHtAOPT2gGJANk4wFAA==", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table[Column1 = _t, Column2 = _t]
    ),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Column1", "Column1 - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Duplicated Column", 
        {{"Column1", type text}, {"Column2", type text}}
    ),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars = true]),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1),
    #"Changed Type1" = Table.TransformColumnTypes(
        #"Added Index", 
        {{"Column", type text}, {"Result", type text}}
    ),
    #"Split Column by Character Transition" = Table.SplitColumn(
        #"Changed Type1", 
        "Column", 
        Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), 
        {"Column.1", "Column.2", "Column.3"}
    ),
    GetSplittedValues = Table.AddColumn(
        #"Split Column by Character Transition", 
        "SplittedValues", 
        each Record.FieldValues(
            Record.SelectFields(
                _, 
                List.Difference(Record.FieldNames(_), Table.ColumnNames(#"Changed Type1"))
            )
        )
    ),
    #"Expanded SplittedValues" = Table.ExpandListColumn(GetSplittedValues, "SplittedValues"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(
        #"Expanded SplittedValues", 
        {{"SplittedValues", each Text.BeforeDelimiter(_, " "), type text}}
    ),
    FilterOnlyRowsWithNumbers = Table.SelectRows(
        #"Extracted Text Before Delimiter", 
        each (List.Contains({"1".."9"}, Text.Start([SplittedValues], 1)))
    ),
    FilterOnlyRowsWithKg = Table.SelectRows(
        FilterOnlyRowsWithNumbers, 
        each Text.Contains([SplittedValues], "kg", Comparer.OrdinalIgnoreCase)
    ),
    #"Added Custom1" = Table.AddColumn(
        FilterOnlyRowsWithKg, 
        "Custom", 
        each try Number.From(Text.BeforeDelimiter([SplittedValues], "kg")) otherwise null
    ),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] <> null)),
    #"Added Suffix" = Table.TransformColumns(
        #"Filtered Rows", 
        {{"Custom", each Text.From(_, "en-GB") & "kg", type text}}
    ),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Added Suffix", 
        {"Column_1", "Result", "Custom"}
    )
in
    #"Removed Other Columns"

 

the real power of PQ + RegEx

let
    RE = (regex as text, str as text) =>
    let
        html =
            "<script>var regex = " & regex & "; var str = """ & str & """; var res = str.match(regex); document.write(res)</script>",
        res = Web.Page(html)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
    in res,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMsxOV4rViVZKTEo2NICyDQ28oQyL7HSFjIwMEzDPMSnZCq7GNDE7PTExyRihXUFXwdAUyjX2dgfTltkQOiwMKJuWrmABlc/OVgAqVTAwgHANDAwfNSzTAxqipwcUB7KVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each RE("/\d+kg/gi", [Column]))
in
    #"Added Custom"