lunes, septiembre 11, 2017

¿Quién se ha llevado mi asistente de importar texto?

El título del post es una paráfrasis del popular libro de Spencer Johnson de fines de los 90. El libro trata de las dificultades de afrontar cambios, tanto en el trabajo como en la vida privada. ¿Cómo está relacionado esto con Excel? Todo usuario veterano sabe muy bien a qué me refiero. A lo largo de los años Excel ha ido evolucionando y moviendo nuestro "queso" (la interface del usuario). Algunos cambios han sido revolucionarios y a su vez traumáticos para gran parte de los usuarios, como la cinta en lugar de las barras de menú en Excel 2007. Otros pasan inadvertidos, por ejemplo el cambio en el asistente de Tablas Dinámicas.

En Excel 2016 los asistentes de importar datos (el de texto entre ellos) no se encuentra en la cinta de opciones. En Excel 2007-2013 aparecían en la pestaña Datos bajo Obtener datos externos



 En Excel 2016 el usuario verá ésto


Es decir, el mecanismo de Power Query, bajo la denominación Obtener y Transformar Datos, ha reemplazado al asistente de importar texto (y otros tipos de archivos).
Hay mucha lógica en esta decisión de Microsoft y todo usuario de Excel obtendrá enormes beneficios de esta herramienta. Dicho ésto hay situaciones en las cuales queremos de todas maneras usar el "viejo" asistente de importar datos. Por ejemplo, en el caso de los espacios múltiples que mostré en la nota anterior.
El asistente no ha sido eliminado sino que no aparece en la cinta. Para poder utilizarlo tenemos que llevar a cabo las siguientes acciones:

1 - En Archivo-Opciones seleccionamos al sección Datos 


2 - Marcamos las opciones de nuestro interés, por ejemplo las cuatro que aparecen en la columna de la izquierda. Finalmente apretamos Aceptar.

Ahora esta opciones estarán a nuestra disposición bajo Datos-Obtener Datos-Asistentes Heredados


jueves, septiembre 07, 2017

Power Query – El caso de los espacios rebeldes

Empiezo con una aclaración: todo el crédito de esta nota corresponde a KenPuls que hace dos años atrás publicó la solución al problema que muestro en este post.

Como sabemos Power Query es la mejor herramienta para extraer y transformar datos. Una bendición para los usuarios de Excel que trabajamos con grandes cantidades de datos de distintas fuentes.
Una de las grandes ventajas del Power Query es permitirnos realizar todo tipo de transformaciones, algunas realmente complejas, desde la interface del usuario (UI).

Una de las transformaciones usuales es dividir una columna usando el espacio entre los valores de la celda como criterio.

En Excel (en este ejemplo uso Excel 2013), por ejemplo, podemos usar usamos la funcionalidad Datos-Obterner Datos Extarnos-Dede texto lo que abre el asistente


Podemos ver claramente que el archivo tiene 6 columnas pero al observar con atención veremos que entre las columnas hay más de un espacio. Y, además, la cantidad de espacios entre cada columna no es constante. Sin embargo ésto no es un problema para el asistente de importar texto



Al señalar "Espacio" como separador Excel ignora todos los espacios inncesarios yrealiza la división en la forma deseada.



Power Query tiene, tal como Excel, esa posibilidad desde la interfaz del usuario (el menú) pero el comportamiento es distinto. Empezamos por crear la consulta editándola en el editor de PQ y usando "Dividir columna" con el separador Espacio

El resultado es totalmente distinto del esperado!!!


Esto se debe a que Power Query, a diferencia de Excel, interpreta cada espacio como separador. A esta altura de los acontecimientos volvemos sobre nuestros pasos echamos manos a Transformar-Recortar (Trim, el equivalente de la función ESPACIOS)


pero si lo intentan verán que tampoco ésto nos ayudará en la tarea. El problema es que Recortar (Trim) del PQ sólo elimina los espacios finales

Aquí es donde Ken Puls viene a nuestro rescate. En la nota que menciono al principio, Ken publica una solución basada en una función de PQ.
Sin más preámbulos, vamos a mostrar como usar la función. En el menú de Power Query iniciamos una consulta en blanco


En la ventana del PQ abrimos el editor avanzado


borramos todo el contenido y pegamos este código

(text as text, optional char_to_trim as text) =>
let
char = if char_to_trim = null then " " else char_to_trim,
split = Text.Split(text, char),
removeblanks = List.Select(split, each _ <> ""),
result=Text.Combine(removeblanks, char)
in
result

y presionamos el botón Listo


En la ventana del editor cambiamos el nombre de la función a algo más significativo, como  PowerTrim


Apretamos Cerrar y Cargar; la función aparece ahora en la ventana de las consultas.


Ahora volvemos a abrir la consulta de los datos, creamos una columna personalizada donde la función ha eliminado los espacios inneesarios; borramos la columna original y aplicamos "Dividir columna" a la columna que acabamos de crear. Todo el proceso puede verse en este video




lunes, agosto 14, 2017

Autofiltro en tablas dinámicas - otro truco

Si bien este post trata sobre Autofiltro en Tablas Dinámicas, la idea surgió de uno de los videos del curso sobre Power Query, Power Pivot y Power BI de Miguel Escobar que recomiendo considerar a todo analista que usa Excel y quiera potenciar su profesionalidad (aclaración: si, recibo una comisión por cada inscripción pero ésto no quita de la calidad del curso).

Volviendo a nuestro tema, en mi post Filtrar por etiquetas en tablas dinámicas con dos criterios mostré como podemos agregar Autofiltro a una tabla dinámica para lograr filtrados que no serían posibles con el filtrado incorporado de la tabla dinámica.

Viendo uno de los videos del curso vi otro beneficio que puede obtenerse con esta técnica. Miguel ha incorporado como bono tres videos de Bill Jelen (Mr. Excel) que se titulan "Tres razones por las qué amo Power Pivot". En uno de ellos Bill muestra otro beneficio que podemos obtener agregando Autofiltro a una tabla dinámica.

Veamos la siguiente situación


Agreguemos ahora una columna que muestre el porcentaje del total de cada cliente como muestro en este video



Ahora podemos ver todos las ventas a los clientes de la vendedora Anna, ordenados de mayor a menor y el peso relativo de cada uno del total.


Si queremos mostrar los cinco clientes más importantes podemos usar el filtro de la tabla dinámica


y en la ventanilla de "Diez mejores" ponemos 5; éste es el resultado


Excel efectivamente nos muestra los cinco clientes con más ventas pero ahora el total general es el de los cinco clientes, no el total general del cuadro anterior y lo mismo sucede con los porcentajes. El cliente Rattlesnake Canyon Grocery que representa el 14.72% de las ventas ahora muestra el 24.44%.

La técnica que mostré en el post que menciono al principio de de esta nota puede ayudarnos a superar este inconveniente.

Si seleccionamos alguna celda de la tabla veremos que la opción Autofiltro está deshabilitada (no así, curiosamente, la opción "borrar" del filtro)


Siguiendo la técnica mencionada, seleccionamos la celda inmediatamente a la derecha de la última etiqueta de las columnas de la tabla. Al hacerlo veremos que ahora podemos aplicar la opción "Filtro" . Al hacerlo el Filtro se aplicará también a los campos de datos de la tabla


Ahora vamos a usar la opción "Diez mejores" del filtro del campo "Ventas" (que antes no existía) para mostrar los cinco principales clientes


La única diferencia con el método anterior es que para mostrar los primero cinco vamos a introducir seis en la definición del filtro (es decir, el número de filas a mostrar más uno)


Podemos ver que con esta técnica el total general y los porcentajes se mantienen.

El motivo por el que elegimos 6 para mostrar 5 es que con esta técnica la fila del total general es una de las incluidas en el recuento, de manera que para mostrar cinco clientes tenemos que definir seis filas.