Replicar la Función SWITCH de Power Pivot

El Caso

Power Pivot tiene una función llamada SWITCH(), VBA usa SELECT CASE y Excel puede utilizar BUSCARV para replicar la misma funcionalidad. Independientemente de como la llamemos, nosotros usamos cada una de estas funciones para realizar lógicas de condiciones múltiples al declarar una tabla de valores índices y resultados para luego buscar dicho valor índice mediante una variable.  La función luego busca el valor brindado (ya sea de Excel/Power Pivot o una rutina en VBA) y devuelve el valor que coincide. Más fácil de mantener que varios niveles anidados de IF, a veces tiene sentido replicar la función SWITCH de Power Pivot en Power Query. La sintaxis de esta función en cada una de las diversas tecnologías luce algo como esto:

  • Excel:=BUSCARV(Valor_buscado,Matriz_buscar_en ,Indicador_columnas,[ordenado])
  • PowerPivot:  =SWITCH(expression,value_1,result_1,[value_2,result_2],…,[Else])
  • VBA:  SELECT CASE:  CASE IS = 1: “Result 1”: CASE ELSE: END SELECT

Descarga el Archivo

Nuestro Objetivo

Un ejemplo donde esto puede ser realmente útil es a la hora de romper patrones codificados como los códigos de facturación al cliente, donde cada letra representa algo específico. Toma un código como el siguiente: “MP010450SP”, donde el noveno letra puede ser uno de los siguientes: E = Employee, S = Yacht Club, N = Non-Taxable, R = Restricted, I = Inactive, L = Social, M = Medical, U = Regular

Para romper esta separación, en Excel, podríamos construir una función con muchos IF anidados y construir un BUSCARV basado en las opciones de letras. En Power Pivot sin embargo, es mucho más fácil con la función SWITCH de la siguiente manera:

=SWITCH([Column], "E","Employee", "S","Yacht Club", "N","Non-Taxable", "R","Restricted", "I","Inactive", "L","Social", "M","Medical", "U","Regular", "Undefined")

Tenga en cuenta que hay MUCHAS maneras de lograr este objetivo. La pregunta que este patrón trata de responder es como crear una función equivalente en Power Query que puedas invocar desde otras consultas.

Paso 1: Replicar la función SWITCH de Power Pivot

La construcción de la función no es demasiado difícil, una vez sepas la estructura básica. Para empezar, tenemos que:

  • Crear una consulta en blanco (Power Query  –> Desde otros orígenes  –> Consulta en blanco)
  • Dale un nombre a la consulta como fnSWITCH
  • Ve a Vista –> Editor avanzado
  • Pega el siguiente código M

(input) => let values = { {result_1, return_value_1}, {input, "Undefined"} }, Result = List.First(List.Select(values, each _{0}=input)){1} in Result

Las partes claves para reconocer aquí son:

  • result_1 es la primera de las posibilidad que podemos pasar A la función
  • return_value_1 es valor que queremos que se devuelve si el primer valor es result_1
  • Si necesitamos más valores, solo necesitamos insertar otra coma después de la sección de value_1 e ingresar una sección para value_2
  • podemos continuar agregando nuevos valores como sea necesario.
  • El valor “Undefined” devolverá el texto “Undefined” (que significa indefinido en español) si el valor que pasamos no está dentro de la lista de opciones que hemos creado (es la parte Else de la función SWITCH)

Utilizando esta estructura, podemos modificar la función fnSWITCH para nuestro escenario de la siguiente manera:

(input) => let values = { {"E", "Employee"}, {"S", "SCYC"}, {"N", "Non-Taxable"}, {"R", "Restricted"}, {"I", "Inactive"}, {"L", "Social"}, {"M", "Medical"}, {"U", "Regular"}, {input, "Undefined"} }, Result = List.First(List.Select(values, each _{0}=input)){1} in Result

Observa que la única diferencia entre los dos es que hemos añadido todas las opciones disponibles. No estás limitado a buscar valores, sólo asegúrate de que las opciones son siempre ingresadas en parejas entre llaves y tienen una coma al final de la línea. Una vez que hayas terminado de hacer las modificaciones:

  • Clic Aceptar
  • Ir a Inicio –> Cerrar y cargar

Paso 2:  Implementando la función fnSWITCH

Primero damos clic dentro de los valores de la tabla y luego vamos a la cinta de  Power Query  –> From Table, lo cual nos brinda la siguiente ventana:

pqChoose_1

Lo siguiente sería extraer la novena letra de la columna BillingCode:

  • Ve a Agregar Columna –> Agregar Columna Personalizada
    • Nombre de columna:  Status
    • Fórmula:  =Text.Range([BillingCode],8,1)

y eso nos brinda el siguiente resultado:

pqChoose_2

Ahora que hemos extraído la letra que queremos, utilicemos nuestra función.

  • Clic en el ícono de engranaje al lado del paso Status
  • Envuelve la fórmula existente con nuestra invocación de función
    • =fnSWITCH(Text.Range([BillingCode],8,1))

Esto nos brinda lo siguiente:

pqChoose_3

Resultados Finales

Encontrarás que todos los códigos de ejemplo funcionan muy bien, y que nada regresa como indefinido (undefined). Si desea ver cómo reacciona la consulta a diferentes artículos, vuelva a la tabla de clientes y trate de cambiar la penúltima letra a otra cosa.  Al actualizar la tabla, encontrarás que evaluará la nueva letra y devuelve el resultado apropiado

Salvedad

Cabe señalar que la función escrita arriba es sensible a mayúsculas, lo que significa que un código de MP010450uP devolvería “Undefined”. En el caso de la fuente original de los datos, esto era de esperar que los códigos válidos se componen de letras mayúsculas. Si quisiéramos aceptar minúsculas y mayúsculas, tendríamos que utilizar la función Text.Range para forzarla a que se lea la letra como mayúscula. Esto resultaría en una invocación de función que lee de la siguiente manera: =fnSWITCH(Text.Upper(Text.Range([BillingCode],8,1))) el cual funcionaría, como se puede ver aquí: pqChoose_4

Patrones para Power Query
Taller de Power Query

este trabajo ha sido compartido 0 veces
 500
Sobre

 Power Query Training

  (13 Artículos)

Somos un equipo comprometido en ofrecer el mejor contenido para los nuevos y experimentados usuarios de una herramienta en la que creemos: Power Query. Estamos firmemente convencidos de que esta es la mejor herramienta que ha sido agregada a Microsoft Excel en un largo tiempo.