Lógica condicional compleja en Power Query

Refiriéndose a la nota anterior me consulta un lector: si se puede hacer lo mismo utilizando select case en caso de que cada vendedor tuviera tramos diferentes de comisión.  Esta consulta puede interpretarse de dos maneras:

• que cada vendedor tenga una diferente escala de comisiones;

• que los vendedores se hicieran acreedores a una comisión del 5% por los primeros 20,000 (nuevamente, la moneda que les venga cómoda), 7.5% por los siguientes 30,000; 9% por los siguientes 50,000 y un 10% por las ventas encima de las 100,000.

Seguramente mi lector se refiere al primer caso ya que si lo quisiéramos resolver con Vba, seguramente usaríamos Select Case. Tendríamos primero que identificar al vendedor y luego aplicar "su" escala de comisiones a su total de ventas.

El segundo caso implica calcular la comisión para cada "tramo" de las ventas, algo así como el impuesto progresivo o escalonado.

En este post vamos a analizar las soluciones posibles con Power Query para el primer caso. En otra nota nos ocuparemos del segundo caso.

Cálculo condicional en Power Query

Casi todo proceso de transformación de datos incluye, en algún momento, un cálculo condicional. Este cálculo puede ser un simple si condición, entonces resultado, sino otro resultado  hasta una compleja serie de SI anidados.

En Excel Clásico disponemos de la función SI donde separamos las condiciones con comas y podemos anidar hasta 64 condiciones (aunque como ya he señalado es preferible usar BUSCARV en lugar de complicarnos la vida, que bastante complicada es aún sin nuestra colaboración).

En Power Query, como era de esperar, podemos usar la interfaz de usuario para escribir fórmulas condicionales

pero en ciertos casos no podemos apoyarnos en la interfaz y tendremos que escribir la fórmula por nuestros propios medios.
En este post voy a mostrar las dos posibilidades.

Configuración de fechas con Power Query

Distintos países usan distintas formas de expresar las fechas. En Argentina o en España, por ejemplo, la fecha de hoy se escribe 22/8/2019 (el modelo DD/MM/AAAA); en los Estados Unidos o en Las Filipinas la misma fecha se escribirá 8/22/2019 (MM/DD/AAAA). Ésto se convierte en un problema cuando un argentino o un español tiene que trabajar con datos enviados de los Estados Unidos.
Más precisamente me sucedió al descargar la lista de los suscriptores a este blog. En la lista aparecen el correo electrónico, la fecha de suscripción y el estatus. Las fechas del archivo aparecen con el formato MM/DD/AAAA. Como quería investigar cuantos suscriptores había ganado en cada año tenía que transformar las fechas. Y cuando de transformar datos se trata, echamos mano al Power Query.

En este post voy a mostrar dos métodos distintos de hacerlo. Pero empecemos por visualizar el problema.

Después de crear la consulta al archivo (en este caso .csv) vemos ésto en la ventana del editor

Power Query - Columnas por ejemplo

En el post anterior vimos como podemos usar Columna por ejemplo  del Power Query para realizar con facilidad tareas complicadas, como extraer números de una cadena alfa-numérica no ordenada.
En este post me voy a extender un poco más sobre esta herramienta tan útil.

Una de las características más sobresalientes de Power Query es que nos permite realizar transformaciones complicadas de datos utilizando la interfaz del usuario. Es decir, sin el más mínimo conocimiento del lenguaje M, el motor del Power Query. Sin embargo, tener conocimientos del lenguaje M, aún mínimos, es muy útil (les invito a leer este post donde cito las etapas del aprendizaje de Power Query según Gil Raviv).

Vamos a ver como Columna por ejemplo puede ayudarnos a aprender algo acerca de las funciones del lenguaje M.

Extraer valores de columnas alfanuméricas con Power Query

En la nota anterior vimos con que facilidad Power Query nos permite extraer valores numéricos de una cadena de texto mixta (letras y números).
En los casos que analizamos el texto a dividir o de donde debíamos extraer los valores, tenía cierto orden; letras y luego números o números y luego letras.
Anticipándome a mis avisados lectores propuse la situación en la cual el texto a dividir, que contiene tanto números como letras, no sigue ningún patrón u orden, como en este ejemplo

Para casos como éste Dividir columna no nos sirve. Pero Power Query no nos deja librados a nuestra amarga suerte; en su baúl de herramientas tenemos Columna a partir de los ejemplos, en el tab Agregar columna