Regex para restar textos por Power Query/ Dax

[ngct1112]

Hola, me gustaría extraer algunos datos de una columna que la entrada de los datos no son consosos.

Puedo saber si hay alguna manera de limpiar los datos por PowerQuery o DAX. Gracias

Columna	Resultado
1kg	1kg
abc10kg	10kg
10kg	10kg
18kg hhh4	18kg
Abc:10kg	10kg
5aaab3kg	5kg
abc - 15kg	15kg
3kg	3kg
9kg	9kg
VV - fg 8kg	8kg
kk 5kg 00g	5kg
001....abc.. 5kg...	5kg

[ImkeF · Accepted solution]

Hola @ngct1112 ,

por favor pegue este código en el editor avanzado y siga los pasos:

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"

[Syndicate_Admin · Accepted solution]

el poder real de 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"

[Syndicate_Admin]

@CNENFRNL funciona pero muy súper mega lento, y nunca completo para filas de 1 mil

[Syndicate_Admin]

Recibo el siguiente error mientras intento editar la fórmula regex

Estoy tratando de comprender lo que has hecho aquí. Pero fallando.

Exporté el código, edité la tabla de origen y cambié la fórmula regex para que coincida con mi requisito. pero obteniendo un error:

= Table.AddColumn(#"Renamed Columns", "Custom", each RE("/\w+", [Idealink]))

Expression.Error: No había suficientes elementos en la enumeración para completar la operación.

[Syndicate_Admin]

No @CNENFRNL,

esto es realmente awesomesauce !!

Ahora solo nos queda esperar y esperar que esto también se pueda refrescar en el servicio algún día.
Parece que también está prohibido a través de una puerta de enlace, por desgracia.

Saludos, Imke

[Syndicate_Admin]

Gracias por su amable recordatorio. De hecho, debo tener en cuenta tales restricciones, ya que ahora soy adicto a una forma tan complicada de manejar regex en PQ en lugar de incrustar scripts de R o Python. 😂

Por cierto, te tengo un gran agradecimiento, ya que estoy iluminado por muchos de sus blogs en PQ! 👍

[Syndicate_Admin]

@CNENFRNL Este método se ve tan brillante.

¿Puedo preguntar cómo funciona? utilizando el parámetro para crear regrex?

[Syndicate_Admin]

Es un poco complicado. Usé Web.Page para analizar html que contiene script js; como puede ver, el objeto regex incrustado se puede usar en dichos fragmentos de código js. Funciona en su scenairo, pero no es una panacea. Pls se refieren a las restricciones en la respuesta de ImkeF.

[amitchandak]

@ImkeF , puede ayudar en esto ?

[ImkeF · Accepted solution]

Hola @ngct1112 ,

por favor pegue este código en el editor avanzado y siga los pasos:

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"

[ngct1112]

@ImkeF la forma en que haces esto es increíble. Apreciado con su ayuda.