Reemplazar varios valores en varias columnas en un solo paso

Power Query
Un usuario Pregunto ✅

elaj

Hola,

tengo una tabla como esa:image.png

y quiero reemplazar los valores de cadena por números como ese:

«Últimas 4 semanas» = 1
«Hace 2 meses» = 2
«Hace 3 meses» = 3
«Hace 4 meses» = 4
… etc

¿Hay alguna manera de reemplazar con estos múltiples valores en múltiples columnas seleccionadas personalizadas?

Solo encontré soluciones para múltiples valores para una columna … o un valor para múltiples columnas.

Gracias por tus respuestas

Rocco_sprmnt21

si tuviéramos una función replacerAny.ReplaceValue …, podríamos hacerlo todo en un solo paso con la función table.replace.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcYxDQAACAMwL7t52EANwb8NFnp1BolAJjYGdMmvXOlbbtW33W7sHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [prima = _t, seconda = _t]),

    cols=Table.ColumnNames(Source),
    Dict=#table({"old","new"},{{1,"uno"},{3,"tre"},{11,"undici"}}),
replaced=Table.ReplaceValue(
    Source,
    Dict[old],Dict[new],
    replacerAnyReplaceValue,
    cols  
)
in
    replaced

mientras tanto podemos usar esto:

let
rep=(value, old as list , new as list )=>
let
Dict=Record.FromList(List.Transform(new, Text.From), List.Transform(old, Text.From)), 
Replacements= Record.FieldOrDefault(Dict,value,value)
in
Replacements
in
rep

Rickmaurinus

Hola,

Reemplazar valores en varias columnas no es la tarea más sencilla. Una forma es usar univot y reemplazar elementos usando List.ReplaceMatchingItems. A continuación, puede hacer referencia a TranslationTable combinando List.ReplaceMatchingItems con List.Zip. Escribo sobre todo esto aquí mismo:

https://gorilla.bi/power-query/replace-values/

¡Espero que ayude!

Almiar

Hola @elaj

Si está descuidado, ¿podría aceptarlo como una solución para cerrar este caso y ayudar a los demás miembros a encontrarlo más rápidamente?

Si no es así, no dudes en hacérmelo saber.

Atentamente

Maggie

Rocco_sprmnt21

si tuviéramos una función replacerAny.ReplaceValue …, podríamos hacerlo todo en un solo paso con la función table.replace.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcYxDQAACAMwL7t52EANwb8NFnp1BolAJjYGdMmvXOlbbtW33W7sHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [prima = _t, seconda = _t]),

    cols=Table.ColumnNames(Source),
    Dict=#table({"old","new"},{{1,"uno"},{3,"tre"},{11,"undici"}}),
replaced=Table.ReplaceValue(
    Source,
    Dict[old],Dict[new],
    replacerAnyReplaceValue,
    cols  
)
in
    replaced

mientras tanto podemos usar esto:

let
rep=(value, old as list , new as list )=>
let
Dict=Record.FromList(List.Transform(new, Text.From), List.Transform(old, Text.From)), 
Replacements= Record.FieldOrDefault(Dict,value,value)
in
Replacements
in
rep

Rocco_sprmnt21

En respuesta a Rocco_sprmnt21

Esta versión es una especie de truco que no utiliza iteraciones. Solo usa la función Table.ReplaceValue, utilizando como cuarto parámetro (el lugar de función sustituto) la función Record.FieldOrDefault modificado solo por el orden de sus parámetros

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcYxDQAACAMwL7t52EANwb8NFnp1BolAJjYGdMmvXOlbbtW33W7sHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [prima = _t, seconda = _t]),

    cols=Table.ColumnNames(Source),
    Dict=[1="uno",3="tre",11="undici"],
replaced=Table.ReplaceValue(
    Source,
    Dict,
    "UnValoreQualsiasi",
    (x,y,z)=>Record.FieldOrDefault(y,x,x),
    cols
)
in
    replaced

Smauro

Hola @elaj,

He tenido demasiado tiempo libre esta mañana, así que creé una función para reemplazar valores basada en una Tabla de transformación.

Lo bueno es que llama a la tabla de destino una vez y a la tabla de transformación una vez, utilizando una función de reemplazo personalizada dentro de Table.ReplaceValue.

Por demasiado tiempo libre, quiero decir que no solo agregué manejo de errores, sino también documentación.

1) Vas a necesitar una tabla de transformación. Debería ser algo que se vea así:

image.png

Los nombres de las columnas son irrelevantes, pero la columna de búsqueda debe ser distinta por razones obvias. Si no se especifica ninguna columna de búsqueda, la primera columna se utiliza como columna de búsqueda y la segunda como reemplazo.

Digamos que nombras esto Transformaciones

2) Agregar la función como una nueva consulta. Usaré una etiqueta de spoiler aquí porque es demasiado larga.

Revelación

let
    TableReplaceValuesFromTableFunction = (table as table, transformationTable as table, optional columns as any, optional specifyTransformationColumns as list, optional missingField as nullable number) as table =>
        
        let
            // Buffer Transformation Table to call it only once
            BufferedTransformationTable = Table.Buffer(transformationTable),
            // Step to add error handling for given table & columns
            tryTransformationColumnNames = try
                let
                    temp = Table.ColumnNames(BufferedTransformationTable),
                    countTargetColumns = List.Count(temp),
                    countSpecifiedColumns = try List.Count(specifyTransformationColumns) otherwise 0
                in
                    if countTargetColumns < 2 then error [Reason = "", Message = "The transformation table needs to have at least two columns", Detail = [TransformationColumns = temp] ]
                    else if countSpecifiedColumns = 0 then {temp{0}, temp{1}}
                    else if countSpecifiedColumns = 2 then
                        let
                            l = List.Intersect({specifyTransformationColumns, temp})
                        in
                            if List.Count(l) = 2 then l else error  [Reason = "", Message = "The transformation table does not contain the 2 specified columns", Detail = [TransformationColumns = temp, SpecifiedColumns = specifyTransformationColumns] ]
                    else error [Reason = "Error in specified transformation columns", Message = "Please specify exactly 2 columns", Detail = [SpecifiedColumns = specifyTransformationColumns ]],
            TransformationColumnNames = tryTransformationColumnNames[Value],

            // Define Replacer Function to trick Table.ReplaceValue
            CustomReplacer =
                (value as any, old as any, new as any) as any =>
                    let
                        getvalue = try
                                Expression.Evaluate(
                                    "tbl{["
                                    & TransformationColumnNames{0}
                                    & " = t]}["
                                    & TransformationColumnNames{1}
                                    & "]",
                                    [ tbl = BufferedTransformationTable, t = value ]
                                ),
                        newvalue = 
                            if getvalue[HasError] then 
                                if Text.Contains(getvalue[Error][Message], "The key matched more than one row in the table") then error [Reason = "An error occured in the Transformation Table", Message = "More than one replacements were found for the specified value", Detail = [ValueToReplace = value]]
                                else if (missingField??1) = 0 then
                                    if Text.Contains(getvalue[Error][Message], "The key didn't match any rows in the table") then error [Reason = "An error occured in the Transformation Table", Message = "A replacement cannot be found for the specified value", Detail = [ValueToReplace = value]]
                                    else error getvalue[Error]
                                else if (missingField??1) = 2 then null
                                else value
                            else getvalue[Value]
                    in
                        newvalue,

            // If table and columns ok, call Table.ReplaceValue
            Result = if tryTransformationColumnNames[HasError] then error tryTransformationColumnNames[Error] else Table.ReplaceValue( table, null, null, CustomReplacer, columns??Table.ColumnNames(table) )
        in
            Result,



    // Add documentation
    TableReplaceValuesFromTableType = type function (
                table as (type table meta [
                    Documentation.FieldCaption ="Target Table",
                    Documentation.FieldDescription = "The table to make replacements"
                    ]),
                transformationTable as (type table meta [
                    Documentation.FieldCaption ="Transformation Table",
                    Documentation.FieldDescription = "The lookup table for replacements"
                    ]),
                optional columns as (type any meta [
                    Documentation.FieldCaption ="Columns",
                    Documentation.FieldDescription = "The columns in which to replace values"
                    ]),
                optional specifyTransformationColumns as (type {text} meta [
                    Documentation.FieldCaption ="Transformation Columns",
                    Documentation.FieldDescription = "Columns to check on Transformation Table. If not specified, Column1 and Column2 are used."
                    ]),
                optional missingField as MissingField.Type
                )
        as table meta
            [Documentation.Name = "Table.ReplaceValuesFromTable",
            Documentation.Description = "Replaces multiple values at once using a lookup table",
            Documentation.LongDescription = "Replaces multiple values at once using a lookup table.#(lf)If no columns are specified for the Transformation Table, the first column is used a a lookup and the second as a replacement. If no columns are specified in the Target Table then the default is all columns",
            Documentation.Category = "Table",
            Documentation.Author = "Spyros Mavroforos",
            Documentation.Examples = {[
                    Description = "Replace values on all columns",
                    Code = "let
    StartingTable =
        Table.FromRecords({
            [a = 1, b = 2],
            [a = 2, b = 2],
            [a = 3, b = 4],
            [a = 4, b = 5]
        }),

    transformationTable = 
        Table.FromRecords({
            [check = 1, replace = ""A""],
            [check = 2, replace = ""B""],
            [check = 3, replace = ""C""],
            [check = 4, replace = ""D""]
        })
in
    Table.ReplaceValuesFromTable(StartingTable, transformationTable)",
                Result = "Table.FromRecords({
            [a = ""A"", b = ""B""],
            [a = ""B"", b = ""B""],
            [a = ""C"", b = ""D""],
            [a = ""D"", b = 5]
        })"
                ],
                [
                    Description = "Replace values on column 'a'",
                    Code = "let
    StartingTable =
        Table.FromRecords({
            [a = 1, b = 2],
            [a = 2, b = 2],
            [a = 3, b = 4],
            [a = 4, b = 5]
        }),

    transformationTable = 
        Table.FromRecords({
            [check = 1, replace = ""A""],
            [check = 2, replace = ""B""],
            [check = 3, replace = ""C""],
            [check = 4, replace = ""D""]
        })
in
    Table.ReplaceValuesFromTable(StartingTable, transformationTable, {""a""})",
                Result = "Table.FromRecords({
            [a = ""A"", b = 2],
            [a = ""B"", b = 2],
            [a = ""C"", b = 3],
            [a = ""D"", b = 5]
        })"
                ],
                [
                    Description = "Replace values on column 'b' by using 'Column2' column as lookup and 'Column1' as replacement",
                    Code = "let
    StartingTable =
        Table.FromRecords({
            [a = ""A"", b = ""B""],
            [a = ""B"", b = ""B""],
            [a = ""C"", b = ""D""],
            [a = ""D"", b = 5]
        }),

    transformationTable = 
        Table.FromRecords({
            [Column1 = 1, Column2 = ""A""],
            [Column1 = 2, Column2 = ""B""],
            [Column1 = 3, Column2 = ""C""],
            [Column1 = 4, Column2 = ""D""]
        })
in
    Table.ReplaceValuesFromTable(StartingTable, transformationTable, {""b""}, {""replace"", ""check""}, MissingField.Error)",
                Result = "Table.FromRecords({
            [a = ""A"", b = 2],
            [a = ""B"", b = 2],
            [a = ""C"", b = 4],
            [a = ""D"", b = error [Reason = """", Message = ""A replacement cannot be found for the specified value"", Detail = [ValueToReplace = 5]]
        })"
                ]}
        ]

in
    Value.ReplaceType(TableReplaceValuesFromTableFunction, TableReplaceValuesFromTableType)

let TableReplaceValuesFromTableFunction = (tabla como tabla, transformaciónTable como tabla, columnas opcionales como cualquiera, opcional especificarTransformationColumns como lista, opcional missingField como número anulable) como tabla => let // Buffer Transformation Table para llamarlo solo una vez BufferedTransformationTable = Table.Buffer (transformaciónTable ), // Paso para agregar el manejo de errores para la tabla y columnas dadas tryTransformationColumnNames = try let temp = Table.ColumnNames (BufferedTransformationTable), countTargetColumns = List.Count (temp), countSpecifiedColumns = try List.Count (especificarTransformationColumns) de lo contrario 0 en if countTargetColumns <2 luego error [Reason = "", Message = "The transformation table needs to have at least two columns", Detail = [TransformationColumns = temp] ]else if countSpecifiedColumns = 0 then {temp {0}, temp {1}} else if countSpecifiedColumns = 2 then let l = List.Intersect ({specifyTransformationColumns, temp}) in if List.Count (l) = 2 then l else error [Reason = "", Message = "The transformation table does not contain the 2 specified columns", Detail = [TransformationColumns = temp, SpecifiedColumns = specifyTransformationColumns] ]más error [Reason = "Error in specified transformation columns", Message = "Please specify exactly 2 columns", Detail = [SpecifiedColumns = specifyTransformationColumns ]]TransformationColumnNames = tryTransformationColumnNames[Value], // Definir la función de reemplazo para engañar a Table.ReplaceValue CustomReplacer = (valor como cualquiera, antiguo como cualquiera, nuevo como cualquiera) como cualquiera => deje getvalue = probar Expression.Evaluate («tbl {[»
& TransformationColumnNames{0}
& » = t]}[»
& TransformationColumnNames{1}
& «]»,
[ tbl = BufferedTransformationTable, t = value ]
), newvalue = si getvalue[HasError] entonces si Text.Contains (getvalue[Error][Message], «La clave coincidió con más de una fila en la tabla») luego error [Reason = «An error occured in the Transformation Table», Message = «More than one replacements were found for the specified value», Detail = [ValueToReplace = value]]else if (missingField ?? 1) = 0 entonces si Text.Contains (getvalue[Error][Message], «La clave no coincide con ninguna fila de la tabla») y luego aparece el error [Reason = «An error occured in the Transformation Table», Message = «A replacement cannot be found for the specified value», Detail = [ValueToReplace = value]]else error getvalue[Error]
else if (missingField ?? 1) = 2 entonces nulo else valor else getvalue[Value]
in newvalue, // Si la tabla y las columnas están bien, llame a Table.ReplaceValue Result = if tryTransformationColumnNames[HasError] entonces error tryTransformationColumnNames[Error] else Table.ReplaceValue (tabla, nulo, nulo, CustomReplacer, columnas ?? Table.ColumnNames (tabla)) en Resultado, // Agregar documentación TableReplaceValuesFromTableType = tipo función (tabla como (tipo tabla meta [
Documentation.FieldCaption =»Target Table»,
Documentation.FieldDescription = «The table to make replacements»
]), transformTable como (tipo tabla meta [
Documentation.FieldCaption =»Transformation Table»,
Documentation.FieldDescription = «The lookup table for replacements»
]), columnas opcionales como (escriba cualquier meta [
Documentation.FieldCaption =»Columns»,
Documentation.FieldDescription = «The columns in which to replace values»
]), opcional especificarTransformationColumns como (escriba {texto} meta [
Documentation.FieldCaption =»Transformation Columns»,
Documentation.FieldDescription = «Columns to check on Transformation Table. If not specified, Column1 and Column2 are used.»
]), missingField opcional como MissingField.Type) como meta de la tabla
[Documentation.Name = «Table.ReplaceValuesFromTable»,
Documentation.Description = «Replaces multiple values at once using a lookup table»,
Documentation.LongDescription = «Replaces multiple values at once using a lookup table.#(lf)If no columns are specified for the Transformation Table, the first column is used a a lookup and the second as a replacement. If no columns are specified in the Target Table then the default is all columns»,
Documentation.Category = «Table»,
Documentation.Author = «Spyros Mavroforos»,
Documentation.Examples = {[
Description = «Replace values on all columns»,
Code = «let
StartingTable =
Table.FromRecords({
[a = 1, b = 2],
[a = 2, b = 2],
[a = 3, b = 4],
[a = 4, b = 5]
}), transformTable = Table.FromRecords ({
[check = 1, replace = «»A»»],
[check = 2, replace = «»B»»],
[check = 3, replace = «»C»»],
[check = 4, replace = «»D»»]
}) en Table.ReplaceValuesFromTable (StartingTable, transformTable) «, Result =» Table.FromRecords ({
[a = «»A»», b = «»B»»],
[a = «»B»», b = «»B»»],
[a = «»C»», b = «»D»»],
[a = «»D»», b = 5]
}) «],
[
Description = «Replace values on column ‘a'»,
Code = «let
StartingTable =
Table.FromRecords({
[a = 1, b = 2],
[a = 2, b = 2],
[a = 3, b = 4],
[a = 4, b = 5]
}), transformTable = Table.FromRecords ({
[check = 1, replace = «»A»»],
[check = 2, replace = «»B»»],
[check = 3, replace = «»C»»],
[check = 4, replace = «»D»»]
}) en Table.ReplaceValuesFromTable (StartingTable, transformTable, {«» a «»}) «, Result =» Table.FromRecords ({
[a = «»A»», b = 2],
[a = «»B»», b = 2],
[a = «»C»», b = 3],
[a = «»D»», b = 5]
}) «],
[
Description = «Replace values on column ‘b’ by using ‘Column2’ column as lookup and ‘Column1’ as replacement»,
Code = «let
StartingTable =
Table.FromRecords({
[a = «»A»», b = «»B»»],
[a = «»B»», b = «»B»»],
[a = «»C»», b = «»D»»],
[a = «»D»», b = 5]
}), transformTable = Table.FromRecords ({
[Column1 = 1, Column2 = «»A»»],
[Column1 = 2, Column2 = «»B»»],
[Column1 = 3, Column2 = «»C»»],
[Column1 = 4, Column2 = «»D»»]
}) en Table.ReplaceValuesFromTable (StartingTable, transformTable, {«» b «»}, {«» reemplazar «», «» comprobar «»}, MissingField.Error) «, Result =» Table.FromRecords ({
[a = «»A»», b = 2],
[a = «»B»», b = 2],
[a = «»C»», b = 4],
[a = «»D»», b = error [Reason = «»»», Message = «»A replacement cannot be found for the specified value»», Detail = [ValueToReplace = 5]]}) «]}]en Value.ReplaceType (TableReplaceValuesFromTableFunction, TableReplaceValuesFromTableType)

Digamos que nombras la consulta TableReplaceValuesFromTable

3) Cambiando tu tabla real. Suponiendo que Paso anterior es su paso anterior, ahora puede agregar un paso llamando a la función:

= TableReplaceValuesFromTable(PreviousStep, Transformations)

Si necesita especificar las columnas, espero que pueda averiguar cómo.

Mejor,

Spyros

eLjoT

En respuesta a Smauro

¡Excelente trabajo!

¡Eres el tonto!

¡Muchas gracias me ahorras mucho tiempo!

¡Salud!

acu-amersaw

En respuesta a Smauro

¡Guau! eso es increíble, ¡literalmente me salvaste el día!

Gracias por compartir esto.

Ese debería ser el SOLUCIÓN ACEPTADA

Rocco_sprmnt21

Otra forma podría ser esta que usa la función table.replacevalue:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcYxDQAACAMwL7t52EANwb8NFnp1BolAJjYGdMmvXOlbbtW33W7sHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [prima = _t, seconda = _t]),
    S=Table.ToRows(Source),
    cols=Table.ColumnNames(Source),
    Dict=[1="uno",3="tre",11="undici",33="tre_e_tre"],
replaced=List.Accumulate(Record.FieldNames(Dict), Source, (s,c)=> Table.ReplaceValue(
    s,
    c,
    Record.Field(Dict,c),
    Replacer.ReplaceValue,
    cols
))
in
    replaced

Aprovecho esta oportunidad para señalar el resultado diferente obtenido con Replace.ReplacerText en lugar de Replace.Replacervalue.

image.png

image.png

lbendlin

El otro día se sugirió desvincular los datos en pares clave / valor, hacer las sustituciones y luego volver a pivotar. Pensé que era una buena idea.

Rocco_sprmnt21

En respuesta a lbendlin

intenta adaptar esto

let

Dict = [a="apple",b="banana",l="lemon"],
TurnTextToList = Table.AddColumn(Text, "Custom", each Text.Split([Text], " ")),
Replacements = Table.AddColumn(TurnTextToList, "Changed Text Expected", each Text.Combine(List.Transform([Custom],each Record.FieldOrDefault(Dict,_,_))," "))


in
Replacements

usando esta función:

Table.ReplaceValue(
    Table.FromRecords({
        [a = 1, b = "hello"],
        [a = 3, b = "goodbye"]
    }),
    "goodbye",
    "world",
    Replacer.ReplaceText,
    {"b"}
)

Rocco_sprmnt21

En respuesta a Rocco_sprmnt21

un borrador por el que empezar:

let
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcYxDQAACAMwL7t52EANwb8NFnp1BolAJjYGdMmvXOlbbtW33W7sHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [prima = _t, seconda = _t]),
    S=Table.ToRows(Source),
    cols=Table.ColumnNames(Source),
    Dict=[1="uno",3="tre",11="undici"],

    Replacements = List.Accumulate(S,{},(s,c)=> s&{List.Transform(c,each Record.FieldOrDefault(Dict,_,_))}),

    t=Table.FromRows(Replacements,cols)
in
    t

Entradas relacionadas

Último viernes fecha

Power Query

Un usuario Pregunto ✅ mb0307 Hola, Como puedo conseguir la fecha del último viernes en una columna personalizada de Power Query por favor? Hoy es…

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *