Power Query Configurable / Dynamic If (instrucción) If

[hnguyen76]

Hola a todos.

Intento crear una lista de configuración que puedo convertir en una sola instrucción y, a continuación, hacer que una columna personalizada ingiere esa lógica y evaluar si es posible. El objetivo detrás de esto es permitir que las condiciones se establezcan fuera de power bi, ya que las reglas y lógicas 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	[Proveedor] - "Muestra1"	Entonces	"Muestra"
Si	[OrigOperatorId] á "brg" y [Sistema] - "AP"	Entonces	"Resultado1"
Si	[Sistema] - "AP" o [Sistema] - "PO"	Entonces	"APPO"

Quiero convertir eso en algo como esto:

si [Proveedor] - "Sample1" a continuación, "Sample" else if [OrigOperatorId] - "brg" y [System] á "AP" entonces "Result1" else if [System] á "AP" o [System] - "PO" y luego "APPO" else ""

Por último, pasarlo a una columna personalizada que evalúa toda la cadena. Así que digamos si tengo una tabla de muestra como esta:

Id	Proveedor	OrigOperatorId	Sistema
1	Muestra2	S3	tú
2	Muestra1	Ir	Grr
3	Muestra3	Brg	Ap
4	Muestra4	Abeja	Ap

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

Id	Proveedor	OrigOperatorId	Sistema	Prueba
1	Muestra2	S3	tú
2	Muestra1	Ir	Grr	Muestra
3	Muestra3	Brg	Ap	Resultado1
4	Muestra4	Abeja	Ap	Appo

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

[v-xuding-msft]

Hola @hnguyen76 ,

Puede crear una columna condicional. Cuando desee cambiar If Statement, solo tiene que hacer clic en ese paso en lugar de cambiar el código manualmente en el Editor avanzado.

Para obtener más información, puede hacer referencia al blog para probarlo.

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

[hnguyen76]

Hola @v-xuding-msft ,

La idea es desarrollar una solución y hacerla "sin contacto". La condición cambia las horas extras y nos gustaría permitir que el propietario de un producto cree sus propias hipótesis/conclusiones que se generarán sin alterar nada dentro de Power BI. Puede suponer que los usuarios no tendrán la capacidad de puede cualquier modificación grande o pequeña a un modelo de producción.

[v-xuding-msft]

Hola @hnguyen76 ,

Desafortunadamente, como sé, no es compatible. Si realmente necesita esta característica implementada, puede enviar una idea en https://ideas.powerbi.com/ideas/.

[hnguyen76]

@v-xuding-msft ,
Tendría que discrepar. 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 la consulta de energía no es suficiente para construir una solución como tal, pero mi intuición dice que es posible.

la lógica de función prevista 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 mi valor de hipótesis (si o si de lo contrario termina)
y'y'i' // Esta sería mi condición para establecer
z - z-i// Este sería mi resultado
3. Haga lógica comparativa para cada fila. Resultado de la devolución de la primera verdadera
4. Bucle iterativo: i-i++

[ImkeF]

Hola @hnguyen76 ,

Estoy de acuerdo, la lógica debería funcionar.
La función "Expression.Evaluate" es capaz de 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 produce un error de excepción.
Tal vez uno tendría que usar otras funciones de expresión aquí.
Tal vez @artemus tiene una idea de cómo hacer que esto funcione?

[artemus]

La limitación del uso de Expression.Evaluate es que se ejecuta de forma predeterminada sin ningún acceso a variables o funciones de biblioteca como Table.SelectRows. El segundo parámetro contiene todos estos dependientes y debe declararlos explícitamente. Puede usar temporalmente #shared para todo, pero Power Bi se negará a cargar cualquier consulta con esto en el modelo.

Por lo general, tendría que definir todos sus dependientes como:

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

Y úsalo como:

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

El [_ á _] agrega el contexto de fila para el acceso a la consulta.

[ImkeF]

Gracias @artemus por respuesta rápida.

En mi código he usado

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

, que busca cumplir sus necesidades.

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

¿Tienes idea de lo que podría causar esto?
Por favor, pruebe mi código y compruebe si recibe este error también.

[artemus]

Aquí está la muestra fija:

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 · Accepted solution]

Gracias @artemus ,

que elude muy bien la combinación de las dependencias forumula con el contexto de fila.

¿Se trata de una limitación general al usar Expression.Evaluate o cree que el error se debe a alguna configuración específica dentro de este ejemplo?

@hnguyen76 ,

Lo he reescrito ligeramente para que sea más fácil intercambiar el código de ejemplo con sus datos reales.
Además, he incluido una declaració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

[artemus]

Es un error, claro y simple, se puede publicar una solicitud para que se fije una página de ideas. Por mucho que me gustaría que esto se arreglara más rápido, tengo que ir a través de los mismos canales que todos los demás para solicitar al equipo de Power Bi que trabaje en cosas... a menos que se trata de un error con el conector del explorador de datos de Azure, ya que el equipo de Power Bi no es el propietario de esa parte del código y puedo hacer correcciones allí (aunque tampoco estoy en ese equipo).

[hnguyen76]

Hola @ImkeF y @artemus ,
Sólo leer sus intercambios mientras ayuda a resolver este problema en el fin de semana es realmente notable. Quiero darles las gracias a ambos!!

He probado la muestra proporcionada y los resultados son los esperados! Creo que esto es 100% un cambiador de juego que permite que las configuraciones externas se hagan sin tocar realmente la solución.

En el paso de evaluación aplicado, incluí un ámbito try-otherwise en caso de que la condición de entrada no sea válida. Si es así, el valor predeterminado es null.

¡Gracias a los dos otra vez!

PS, 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. ¡hágamelo saber!

[artemus]

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

Para el paso GenerateFunctions, el código debe tener el siguiente aspecto:

..., cada uno prueba Expressoin.Evaluate("cada ...", ...) de lo contrario cada nulo

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

[ImkeF]

Excelente para escuchar @hnguyen76 !

Ya he informado del error aquí: https://community.powerbi.com/t5/Issues/Exception-error-through-environment-in-Expression-Evaluate/i...

[hnguyen76]

@artemus, he agregado el segundo try-otherwise en el GenerateFunctions basado en su sugerencia. ¡Gracias!

@ImkeF,muchas gracias!

[hnguyen76]

@ImkeF ,

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

[ImkeF]

Hola @hnguyen76 ,

no estoy seguro de entender.

Sus condiciones deben evaluarse fila por fila.

¿Dónde ve potencial para omitir cualquier evaluación?

[hnguyen76]

Hola @ImkeF ,

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

Así que para esbozar un poco, tengo alrededor de 100 archivos dentro de una carpeta que estoy ingiriendo y la forma en que actualmente está evalando / cargando es como tal:
1. ConditionsQuery.xlsx (3kb)
2. FileA (1kb)
3. ConditionsQuery.xlsx (1.2mb)
4. ArchivoB (1kb)
5. ConditionsQuery.xlsx (2.89 mb)
6. FileC (1kb)
7. ConditionsQuery.xlsx (4.3 mb)
...
etcetera.
8. ConditionsQuery.xlsx (189 mb)

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

1. ConditionsQuery.xlsx (3 kb)
2. FileA
3. ArchivoB
4. FileC
5. Archivo

...
etcetera.

En cualquier caso, después de jugar con él un poco más, sabía 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 EVALUATE tampoco funcionó. Así que creo que agregando Table.Buffer() en el paso antes de que el paso aplicado EVALUATE esté funcionando (creo) hehehe. Ya no veo que se está cargando continuamente la consulta de condiciones más de una vez.

[ImkeF]

Hola @hnguyen76 ,

¿siempre serán iguales las condiciones ?

[hnguyen76]

Hola @ImkeF ,
Gracias por el apoyo. Puede haber una sola vez donde es Text.Contains([SampleField], "ABC", Comparer.OrdinalIgnoreCase)