Power Query Configurable / Dinámico If Declaración

Un usuario Pregunto ✅

hguyen76

Hola a todos.

Estoy intentando crear una lista de configuración que pueda convertir en una sola declaración y luego hacer que una columna personalizada ingiera esa lógica y la evalúe si es posible. El objetivo detrás de esto es permitir que se establezcan condiciones fuera del poder bi ya que las reglas y la lógica cambiarán con el tiempo sin necesidad de tocar la solución y hacerla más dinámica. Tengo una tabla de muestra a continuación que contiene información clave:

Hipótesis Condición Conclusión Resultado
Si [Vendor] = «Muestra1» entonces «Muestra»
Si [OrigOperatorId] = «brg» y [System] = «AP» entonces «Resultado1»
Si [System] = «AP» o [System] = «PO» entonces «APO»

Quiero convertir eso en algo como esto:

Si [Vendor] = «Muestra1» luego «Muestra» si no [OrigOperatorId] = «brg» y [System] = «AP» luego «Resultado1» si no [System] = «AP» o [System] = «PO» luego «APPO» sino «»

Finalmente, pasar eso a una columna personalizada que evalúa la cadena completa. Entonces, digamos que tengo una tabla de muestra como esta:

IDENTIFICACIÓN Vendedor OrigOperatorId Sistema
1 Muestra2 S3 Hombre
2 Muestra1 Vamos grR
3 Muestra3 brg punto de acceso
4 Muestra4 abeja punto de acceso

La nueva columna condicional debe evaluar y devolver el resultado esperado de:

IDENTIFICACIÓN Vendedor OrigOperatorId Sistema Prueba
1 Muestra2 S3 Hombre
2 Muestra1 Vamos grR Muestra
3 Muestra3 brg punto de acceso resultado1
4 Muestra4 abeja punto de acceso APO

¿Es esto posible? Creo que debería ser. ¡Cualquier ayuda sería muy apreciada!
@ImkeF

ImkeF

En respuesta a artemus

Gracias @artemus,

eso elude muy bien la combinación de las dependencias de la fórmula con el contexto de la fila.

¿Es esta una limitación general al usar Expression.Evaluate o cree que el error se debe a algunas configuraciones específicas dentro de esta muestra?

@hnguyen76 ,

Lo he reescrito ligeramente para que sea más fácil intercambiar el código de muestra con los datos reales.
Además, he incluido una instrucción «List.Select» para obtener el valor correcto de las condiciones evaluadas:

let
    ConditionsFromExcelTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxT0lGKDkvNS8kvilWwVYhRCk7MLchJNYxRAkqUZKTmASmIkFKsDky9f1Fmun9BalFiSX6RZwpEX1JReoySQmJeikJ0cGVxSWouRNgxANmkoNTi0pwSQySj0NUq5BehGhDgj2yAY0CAP0J3SGpFiZ5zfl5JYmZesQbMGzoKcG8YxShpYvojFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hypothesis = _t, Condition = _t, Conclusion = _t, Result = _t]),
    
    AddFunctionColumToConditions = let
        Source = ConditionsFromExcelTable,
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hypothesis", type text}, {"Condition", type text}, {"Conclusion", type text}, {"Result", type text}}),
        GenerateFunctions = Table.AddColumn(#"Changed Type", "Function", each Expression.Evaluate("each if " & [Condition] & " then """ & [Result] & """ else null", [Text.Contains = Text.Contains, Comparer.OrdinalIgnoreCase = Comparer.OrdinalIgnoreCase]))
    in
        GenerateFunctions,

    SourceData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzC3ISTUCsYyBhG9inlKsTrSSEVwKpCg9H0i4FwWBpYzhUiBWUlE6kHQMAEuZwKVArKTUVKhULAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Vendor = _t, OrigOperatorId = _t, System = _t]),
    #"Changed Type" = Table.TransformColumnTypes(SourceData,{{"Vendor", type text}, {"OrigOperatorId", type text}, {"System", type text}}),

    Evaluate = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each List.First(List.Select(List.Transform(AddFunctionColumToConditions[Function], (fn) => fn(_)), each _ <> null))
        )
in
    Evaluate

Hola @hnguyen76,

Podría crear una columna condicional. Cuando quieres cambiar Si es una declaración, solo necesita hacer clic en ese paso en lugar de cambiar su código manualmente en el Editor avanzado.

Para obtener más información, puede consultar el blog para intentarlo.

https://radacad.com/conditional-column-in-power-bi-using-power-query-you-can-do-anything

hguyen76

En respuesta a v-xuding-msft

Hola @v-xuding-msft,

La idea es desarrollar una solución y hacerla «sin contacto». La condición cambia con el tiempo y nos gustaría permitir que un propietario de producto cree sus propias hipótesis/conclusiones para que se generen sin alterar nada dentro de Power BI. Puede suponer que los usuarios no tener la capacidad de realizar cualquier modificación, ya sea grande o pequeña, en un modelo de producción.

En respuesta a hguyen76

Hola @hnguyen76,

Desafortunadamente, como sé, no es compatible. Si realmente necesita implementar esta función, puede enviar una idea en https://ideas.powerbi.com/ideas/.

hguyen76

En respuesta a v-xuding-msft

@v-xuding-msft,
Tendría que estar en desacuerdo. Creo que tenemos todas las piezas necesarias. Actualmente tenemos la capacidad de agregar variables y funciones dentro de una columna personalizada. Reconozco plenamente que mi conocimiento actual de Power Query no es suficiente para crear una solución como tal, pero mi intuición dice que es posible.

la lógica de la función anticipada sería algo de este tipo:

1. cntrows = contar filas en la tabla de configuración
2. para i=1 a cntrows
x = x{i} // Este sería el valor de mi hipótesis (si o si no termina)
y= y{i} // Esta sería mi condición para establecer
z = z{i} // Este sería mi resultado
3. Haz lógica comparativa para cada fila. Devuelve el resultado del primer verdadero
4. Bucle iterativo: i=i++

ImkeF

En respuesta a hguyen76

Hola @hnguyen76,

Estoy de acuerdo, la lógica debería funcionar.
La función «Expression.Evaluate» puede evaluar expresiones de cadena como código M.

Así que sintácticamente, esto debería funcionar:

let
    Conditions = let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxT0lGKDkvNS8kvilWwVYhRCk7MLchJNYxRAkqUZKTmASmIkFKsDky9f1Fmun9BalFiSX6RZwpEX1JReoySQmJeikJ0cGVxSWouRNgxANmkoNTi0pwSQySj0NUq5BehGhDgj2yAY0CAP0J3SGpFiZ5zfl5JYmZesQbMGzoKcG8YxShpYvojFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hypothesis = _t, Condition = _t, Conclusion = _t, Result = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hypothesis", type text}, {"Condition", type text}, {"Conclusion", type text}, {"Result", type text}})
    in
        #"Changed Type",
    ListOfConditions = Table.ToRows(Conditions),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzC3ISTUCsYyBhG9inlKsTrSSEVwKpCg9H0i4FwWBpYzhUiBWUlE6kHQMAEuZwKVArKTUVKhULAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Vendor = _t, OrigOperatorId = _t, System = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {"OrigOperatorId", type text}, {"System", type text}}),
    Evaluate = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each List.First(
                List.Transform(
                    ListOfConditions, 
                    (l) =>  Expression.Evaluate(
                                " if " & l{1} & " then """ & l{3} & """ else null", 
                                [Text.Contains = Text.Contains, _=_]
                            )
                    )
                )
            )
in
    Evaluate

Sin embargo, esto arroja un error de excepción.
Tal vez uno tendría que usar otras funciones de expresión aquí.
¿Quizás @artemus tiene una idea de cómo hacer que esto funcione?

En respuesta a ImkeF

La limitación de usar Expression.Evaluate es que, de forma predeterminada, se ejecuta sin acceso a variables o funciones de biblioteca como Table.SelectRows. El segundo parámetro contiene todas estas dependencias y debe declararlas explícitamente. Puede usar temporalmente #shared para todo, pero Power Bi se negará a cargar cualquier consulta que use esto en el modelo.

En general, necesitaría definir todas sus dependencias como:

let
   Environment = 
   [
      Text.StartsWith = Text.StartsWith,
      List.Contains = List.Contains,
      ...//Add more as needed
   ]

Y usarlo como:

Table.AddColumn("Custom code", each Expression.Evaluate([CodeColumn], Environment & [_ = _])

El [_ = _] agrega el contexto de fila para acceder a la consulta.

ImkeF

En respuesta a artemus

Gracias @artemus por la rápida respuesta.

En mi código he usado

[Text.Contains = Text.Contains, _=_]

que busca cumplir con sus requisitos.

Aún así, recibo un error de excepción:

imagen.png

¿Tienes alguna idea de qué podría causar esto?
Pruebe mi código y verifique si también recibe este error.

En respuesta a ImkeF

Aquí está la muestra arreglada:

let
    Conditions = let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxT0lGKDkvNS8kvilWwVYhRCk7MLchJNYxRAkqUZKTmASmIkFKsDky9f1Fmun9BalFiSX6RZwpEX1JReoySQmJeikJ0cGVxSWouRNgxANmkoNTi0pwSQySj0NUq5BehGhDgj2yAY0CAP0J3SGpFiZ5zfl5JYmZesQbMGzoKcG8YxShpYvojFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hypothesis = _t, Condition = _t, Conclusion = _t, Result = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hypothesis", type text}, {"Condition", type text}, {"Conclusion", type text}, {"Result", type text}}),
        GenerateFunctions = Table.AddColumn(#"Changed Type", "Function", each Expression.Evaluate("each if " & [Condition] & " then """ & [Result] & """ else null", [Text.Contains = Text.Contains]))
    in
        GenerateFunctions,
    ListOfConditions = Table.ToRows(Conditions),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzC3ISTUCsYyBhG9inlKsTrSSEVwKpCg9H0i4FwWBpYzhUiBWUlE6kHQMAEuZwKVArKTUVKhULAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Vendor = _t, OrigOperatorId = _t, System = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {"OrigOperatorId", type text}, {"System", type text}}),
    Evaluate = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each List.First(List.Transform(Conditions[Function], (fn) => fn(_)), each _ <> null)
        )
in
    Evaluate

ImkeF

En respuesta a artemus

Gracias @artemus,

eso elude muy bien la combinación de las dependencias de la fórmula con el contexto de la fila.

¿Es esta una limitación general al usar Expression.Evaluate o cree que el error se debe a algunas configuraciones específicas dentro de esta muestra?

@hnguyen76 ,

Lo he reescrito ligeramente para que sea más fácil intercambiar el código de muestra con los datos reales.
Además, he incluido una instrucción «List.Select» para obtener el valor correcto de las condiciones evaluadas:

let
    ConditionsFromExcelTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxT0lGKDkvNS8kvilWwVYhRCk7MLchJNYxRAkqUZKTmASmIkFKsDky9f1Fmun9BalFiSX6RZwpEX1JReoySQmJeikJ0cGVxSWouRNgxANmkoNTi0pwSQySj0NUq5BehGhDgj2yAY0CAP0J3SGpFiZ5zfl5JYmZesQbMGzoKcG8YxShpYvojFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hypothesis = _t, Condition = _t, Conclusion = _t, Result = _t]),
    
    AddFunctionColumToConditions = let
        Source = ConditionsFromExcelTable,
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hypothesis", type text}, {"Condition", type text}, {"Conclusion", type text}, {"Result", type text}}),
        GenerateFunctions = Table.AddColumn(#"Changed Type", "Function", each Expression.Evaluate("each if " & [Condition] & " then """ & [Result] & """ else null", [Text.Contains = Text.Contains, Comparer.OrdinalIgnoreCase = Comparer.OrdinalIgnoreCase]))
    in
        GenerateFunctions,

    SourceData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzC3ISTUCsYyBhG9inlKsTrSSEVwKpCg9H0i4FwWBpYzhUiBWUlE6kHQMAEuZwKVArKTUVKhULAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Vendor = _t, OrigOperatorId = _t, System = _t]),
    #"Changed Type" = Table.TransformColumnTypes(SourceData,{{"Vendor", type text}, {"OrigOperatorId", type text}, {"System", type text}}),

    Evaluate = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each List.First(List.Select(List.Transform(AddFunctionColumToConditions[Function], (fn) => fn(_)), each _ <> null))
        )
in
    Evaluate

En respuesta a ImkeF

Es un error, claro y simple, puede publicar una solicitud para corregirlo en la página de ideas. Por mucho que me gustaría que estas cosas se solucionen más rápido, tengo que pasar por los mismos canales que todos los demás para solicitar al equipo de Power Bi que trabaje en cosas… a menos que sea un error con el conector del explorador de datos de Azure, como el Power Bi team no posee esa parte del código, y puedo hacer arreglos allí (aunque tampoco estoy en ese equipo).

hguyen76

En respuesta a artemus

Hola @ImkeF y @artemus,
Solo leer sus intercambios mientras ayuda a resolver este problema durante el fin de semana es realmente notable. ¡¡Quiero agradecerles a los dos!!

¡He probado la muestra proporcionada y los resultados son los esperados! Creo que esto es 100% un cambio de juego que permite realizar configuraciones externas sin tocar la solución.

En el paso de evaluación aplicada, incluí un alcance de prueba de lo contrario en caso de que la condición de entrada no sea válida. Si es así, el valor predeterminado es nulo.

¡Gracias a los dos de nuevo!

PD: ImkeF, ¿quieres que cree una publicación sobre el error? Sinceramente, no estoy seguro de qué se trata el error, pero puedo iniciarlo y vincularlo a este hilo. Déjame ¡saber!

En respuesta a hguyen76

También querrá probar lo contrario en el paso GenerateFunctions, ya que esto puede fallar si la sintaxis es incorrecta. El paso de evaluación solo fallará si el código se ve bien, pero hay una referencia a una columna que falta o si se usa un tipo de datos incorrecto.

Para el paso GenerateFunctions, su código debería verse así:

…, cada prueba Expressoin.Evaluate(«cada …», …) de lo contrario cada nulo

En eso, si el texto de la función no es válido, desea devolver una función que siempre devuelve nulo.

ImkeF

En respuesta a artemus

¡Qué bueno escuchar a @hnguyen76!

Ya informé el error aquí: https://community.powerbi.com/t5/Issues/Exception-error-through-environment-in-Expression-Evaluate/i…

hguyen76

En respuesta a ImkeF

@artemus, he agregado el segundo intento, de lo contrario, en GenerateFunctions según su sugerencia. ¡Gracias!

@ImkeF, ¡muchas gracias!

hguyen76

En respuesta a hguyen76

@ImkeF ,

Pregunta: He notado que la función llama/evalúa mi consulta de condiciones nuevamente después de cada archivo dentro de la carpeta de origen. Intenté agregar un List.Buffer o Table.Buffer pero no tuve éxito. Entonces, ¿es posible cargar las condiciones una vez y hacer referencia a eso por fila?

ImkeF

En respuesta a hguyen76

Hola @hnguyen76,

No estoy seguro si entiendo.

Sus condiciones deben evaluarse fila por fila.

¿Dónde ve potencial para saltarse alguna evaluación?

hguyen76

En respuesta a ImkeF

Hola @ImkeF,

Lo siento, mi publicación anterior puede haber sonado confusa, jejeje. No quería omitir ninguna evaluación, sino que quería encontrar una manera de cargar la consulta de condición solo una vez en la memoria.

Entonces, para resumir un poco, tengo alrededor de 100 archivos dentro de una carpeta que estoy procesando y la forma en que actualmente se está evaluando/cargando es la siguiente:
1. CondicionesConsulta.xlsx (3kb)
2. Archivo A (1kb)
3. CondicionesConsulta.xlsx (1.2mb)
4. ArchivoB (1kb)
5. CondicionesConsulta.xlsx (2.89 mb)
6. ArchivoC (1kb)
7. CondicionesConsulta.xlsx (4.3 mb)

etc
8. CondicionesConsulta.xlsx (189 megabyte)

Como puede ver, a medida que se carga cada nuevo archivo, mi consulta de condiciones se llama una y otra vez a partir de 3kb y sigue subiendo y subiendo. Entonces, la forma en que quiero que se cargue idealmente sería:

1. CondicionesConsulta.xlsx (3 kb)
2. Archivo A
3. ArchivoB
4. ArchivoC
5. ARCHIVO


etc

En cualquier caso, después de jugar un poco más, supe que quería usar algún tipo de búfer. Después de intentar usar List.Buffer() no funcionó de la manera que había planeado y Table.Buffer() en el paso EVALUAR tampoco funcionó. Así que creo que al agregar Table.Buffer() en el paso anterior al paso aplicado EVALUAR está funcionando (creo) jejeje. Ya no veo que esté cargando continuamente la consulta de condiciones más de una vez.

ImkeF

Hola @hnguyen76,

¿las condiciones siempre serán igualdades («=») ?

hguyen76

En respuesta a ImkeF

Hola @ImkeF,
Gracias por el apoyo. Puede haber una excepción en la que sea Text.Contains([SampleField], «A B C», Comparer.OrdinalIgnoreCase)

Deja un comentario

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