Iniciemos el día de hoy con una pequeña historia…
Juan es un usuario Avanzado de Excel, de él dependen muchas de las decisiones que se toman en su compañía a nivel financiero y estratégico, puesto que él es el encargado de crear modelos de datos para análisis de escenarios y reportes sobre los indicadores de la compañía, a pesar de ser muy eficiente en su trabajo, Juan, quiere optimizar realmente sus habilidades para la Extracción, Transformación y Carga de Datos puesto que ha notado ciertos procedimientos repetitivos pero críticos para la creación de sus soluciones en Excel en esta etapa de preparación de los datos.
Pues bien, debido al conocimiento que Juan ha adquirido a través del tiempo en Excel, ha logrado recrear este procedimiento de Extracción Transformación y Carga de los datos a través de diferentes funcionalidades como, por ejemplo:
- Creación de Funciones Avanzadas (Matriciales y otro tipo de Alquimia)
- Código VBA
- Otras funcionalidades de Excel, como por ejemplo el grupo Herramientas de Datos
Sin embargo, con el tiempo Juan comprendió que crear dicho procedimiento con estas herramientas puede convertirse en una tarea hecha, solo para algunos expertos en el manejo de nuestra hoja de cálculo y su arsenal de funcionalidades, debido a que se ha tenido que enfrentar a algunos de los siguientes retos:
- Importar y/o relacionar tablas de diferentes fuentes como, por ejemplo, Bases de Datos en Access y SQL Server, así como archivos de texto, CSV o incluso información de fuentes externas como páginas Web.
- Transformar cada tabla en el Formato Tabular adecuado a través de la limpieza y estandarización de cada una de sus columnas no es una tarea fácil. Si tenemos en cuenta que los diferentes orígenes de datos tienen sus propios lineamientos para de manejo de información.
- Enriquecer los datos, según las necesidades que se tengan con columnas auxiliares.
- Con los datos listos y adecuados en un formato tabular, es momento de crear el reporte o la solución que sea requerida, sin embargo, frecuentemente (ya sea mensual o semanalmente) llegan datos y el proceso de actualización se debe repetir manualmente.
Pues bien, al analizar toda esta situación Juan ha obtenido las siguientes conclusiones:
- Juan gasta 35 minutos en la extracción, limpieza y carga de datos a un modelo de análisis que ha construido previamente.
- Adicional a ello, cada semana debe construir un reporte con los indicadores financieros de la compañía actualizados, para el procedimiento de ETL, le tarda al menos 45 minutos
Por lo que Juan concluye que tarda semanalmente 80 Minutos en el proceso de ETL, hasta el momento nada alarmante, existen usuarios que pueden incluso tardar horas en esta tarea, más o menos tiempo, depende todo de nuestras habilidades para Extraer, Transformar y Limpiar nuestros datos a través de Excel, pero no nos desviemos, continuemos con la historia de juan …
Ahora bien, Juan debe repetir este procedimiento una vez por semana, con lo cual tenemos que Juan gasta 320 minutos al mes extrayendo y limpiando datos, al año esta cifra termina siendo aún más alarmante, 3840 minutos, lo que es equivalente a 2, 6 días, es decir que juan pasa 2,6 días del año Extrayendo y Limpiando Datos…
Ahora haz el mismo ejercicio de Juan, … no importa en qué área o nivel de tu compañía te encuentres … la idea es que entiendas cuanto tiempo estas desaprovechando, repitiendo la etapa de limpieza de datos.
Como Juan somos muchos los usuarios de Excel, que sabemos lo difícil que puede llegar a convertirse la etapa de preparación de datos, para crear una solución, sin embargo, gracias a la aparición de Power Query, este procedimiento ya no será un dolor de cabeza
Pero entendamos …
¿Qué es Power Query?
Es una funcionalidad o complemento (depende de la versión de Excel) que pone a nuestra disposición las opciones suficientes para tomar Raw Data (datos con una estructura no óptima para ser analizados) consolidarlos, transformarlos, enriquecerlos, limpiarlos, estandarizarlos y adaptarlos para que queden en un formato tabular apropiado, en palabras simples, Power Query prepara los datos para el consumo en Excel o el Modelo de Datos, todo a través de una interfaz amigable y fácil manejo, con el principal beneficio que este proceso lo podemos automatizar.
Nota: Definición adaptada del libro: El ADN de Power Pivot
Para aclarar un poco …
¿Como encuentrás Power Query en las diferentes Versiones de Excel?
- En Excel 2010 y 2013, aparece como un complemento de Excel, en el siguiente enlace se encuentra el link de descarga gratuita: https://www.microsoft.com/es-co/download/details.aspx?id=39379
- En Excel 2016, Power Query paso a ser una funcionalidad propia de nuestra Hoja de Cálculo, ahora en un gurpo en la pestaña DATIS conocido como OBTENER Y TRANSFORMAR. (Por cierto, este nuevo nombre (Obtener y Tranformar) es menos diciente que Power Query, pero bueno lo importante es que la herramienta esta)
- Al igual que en Excel 2016, Power BI Desktop, utiliza la funcionalidad de Obtener y Transformar para importar, transformar y cargar datos, por lo que su aplicación y conocimiento se extiende a esta nueva y maravillosa solución de Microsoft para hacer inteligencia de negocios. (Al igual que Power Pivot)
Ten en cuenta que Power Query u Obtener y Transformar funciona de la misma manera en las diferentes tecnologías de Microsoft BI, veamos cómo puede facilitarnos la vida a través de un sencillo ejemplo.
Ejemplo – Parque de Diversiones
Imagina que antes de crear un reporte que sea fácil de actualizar y permita analizar el número de asistentes, así como los ingresos obtenidos en un parque de diversiones, tienes que limpiar y preparar los datos que se encuentran en el siguiente archivo de texto con toda la información correspondiente al mes de febrero.
Nota: la segunda columna muestra el número de personas que utilizaron esa atracción en un día especifico del mes, sin embargo, vemos que el nombre no es claro, por otra parte, no hay un delimitador definido entre las columnas, pues los espacios que hay son aleatorios.
Como ya podrás intuir, la tabla del archivo, no tiene un formato tabular apropiado, por lo que debemos preparar y limpiar los datos a utilizando Power Query
1. Importar Tabla a Power Query (Extract)
Para importar los datos o abrir el editor de consultas en la versión de Excel 2016, nos dirigimos a la pestaña Datos, grupo Obtener y Transformar, damos clic en Nueva Consulta, luego elegimos Desde un Archivo y seleccionamos la opción, Desde un Archivo de Texto.
Y en la ventana Importar Datos, seleccionamos la dirección del archivo de texto que contiene la tabla y damos clic en Importar.
Automáticamente aparece una interfaz, con la vista previa de cómo se ve la tabla, en consecuencia, podemos confirmar que no tiene un formato Tabular, por lo que debemos pasar a la siguiente etapa y transformar la tabla, para ello vamos a abrir el editor de consultas, dando clic en el botón Editar.
En este momento, aparece el lugar donde la magia ocurre, el editor de consultas de Power Query.
Aquí vamos a realizar toda la etapa de transformación, así que empecemos con el procedimiento.
2. Transformación en Power Query (Transform)
Primero: Quitar primera y segunda fila, como puedes ver, la primera fila del archivo no pertenece a la tabla, por lo cual debemos eliminarla. La segunda fila hace referencia a las etiquetas, pero vamos a quitarlas por que inevitablemente tendremos que reescribir la etiqueta de cada columna
En el editor de consultas -> Pestaña Inicio -> grupo Reducir Filas -> Quitar Filas – > Quitar Filas Superiores -> en el cuadro de dialogo que aparece, colocamos el número de filas de arriba hacia abajo que queremos quitar, para este ejemplo 1 –> Aceptar
Segundo: Quitar Fila Inferior: Sabemos que al final de la tabla existe una fila que no pertenece a la misma, para quitarla
En el editor de consultas -> Pestaña Inicio -> grupo Reducir Filas -> Quitar Filas – > Quitar Filas Inferiores -> en el cuadro de dialogo que aparece, colocamos el número de filas de abajo hacia arriba que queremos quitar, para este ejemplo 1 –> Aceptar
Tercero: Dividir Columnas, Como podemos ver en el editor de consultas, las columnas de la tabla no están delimitadas claramente por ningún carácter, por lo que un camino sencillo para la solución de este problema, es dividir la columna, cada cierto número de caracteres:
En el editor de consultas seleccionamos la columna con toda la información dando clic sobre su etiqueta- > luego pestaña Transformar -> grupo Coluumna de texto -> Dividir Columna -> por número de caracteres.
En el cuadro de diálogo Dividir la columna por el número de caracteres -> especificamos el número de caracteres como 25 (Utilizamos esta cantidad, porque no sabemos el número exacto de caracteres que se necesitan para dividir la columna) -> habilitamos la opción Una Vez, lo más a la izquierda posible -> Aceptar
Y vemos como se ha divido la primera columna satisfactoriamente…
Para dividir la segunda columna fácilmente, vamos a quitar los espacios extra al principio de la cadena de texto
Clic derecho sobre la columna -> Transformar –> Recortar
Ahora bien, aún falta dividir la segunda columna en los cuatro campos restantes (Asistentes, Fecha, Categoría y Precio de entrada) por lo que vamos a dividir nuevamente la columna cada cierto número de caracteres, en diferentes ocasiones.
Cuarto: Dividir Columna Asistentes
Quinto: Columna Fecha
Recuerda remover los espacios iniciales, luego, dividimos la columna restante con 10 caracteres.
Y al dividir la columna, Power Query identifica que se trata de una columna de fecha.
Nota: Observa, como todas las transformaciones quedan almacenadas en Pasos aplicados, esto facilita la edición de la limpieza de los datos, para futuras actualizaciones.
Para dividir la columna restante (columna1.2.2.2) en la imagen , vamos a repetir el procedimiento, (Quitar espacios extra al principio de la cadena y dividir la columna cada 10 caracteres)
Y Bang¡¡¡, hemos dividió fácilmente, nuestras columnas, por ultimo, no debemos olvidar eliminar las columnas innecesarias.
Renombramos cada columna…
Nota: Para modificar el nombre de un campo, damos clic izquierdo sobre su etiqueta y luego sobrescribimos el nombre.
Y verificamos el tipo de dato, de cada columna.
Sexto: Reemplazar Valor.
Aunque pareciera que hemos terminado, si pausamos un momento, podemos identificar que el ítem, montaña rusa en lugar de tener la letra ñ tiene el carácter ¤, por lo que debemos reemplazarlo.
En el editor de consultas seleccionamos la columna Atracción (o la columna en la cual quiero reemplazar los valores -> pestaña Inicio -> grupo Transformar -> damos clic en el comando Reemplazar los Valores -> en el cuadro de dialogo que aparece, escribimos el valor a reemplazar y el nuevo valor
iY pummmmmmmm Mira como se ha reemplazado este molesto carácter en toda la columna, muy fácilmente.
3. Cargar (Load)
Con las transformaciones ya hechas, vamos a cargar la consulta como una tabla estructurada de Excel.
Verificamos el nombre de la consulta -> Cerrar y Cargar
Y como ya sabemos, Power Query clona los datos en una tabla estructurada de Excel, lista para ser utilizada en cualquier reporte.
Nota: La tabla estructurada tiene el mismo nombre de la consulta, con la cual se ha creado.
Construcción del Reporte
Con lo anterior damos por terminada, toda la etapa de Extracción, Transformación y Carga de los datos, por lo que ahora vamos a crear nuestro reporte …
Como bien sabemos, nada mejor que las tablas dinámicas para resumir nuestros datos, por lo que vamos a crear dos tablas dinámicas en hojas diferentes a partir de la tabla estructurada que recién hemos creado
- La primera con el campo Fecha en el área de filas, el campo Atracción al área de columnas, y por último, el campo número de asistentes al área de valores
- En la segunda tabla dinámica, situamos el campo Atracción en el área de filas y el campo Número de asistentes al área de valores
Agrupar Elementos del Reporte
A partir de las dos tablas dinámicas que hemos creado, vamos a insertar dos gráficos dinámicos, uno de líneas y otro de columnas respectivamente (No olvides personalizarlos). Así como un Slicer vinculado al campo Atracción y que esté relacionado con la primera tabla dinámica.
Agrupamos todo en una hoja nueva Y este es el resultado que obtenemos:
El gráfico de líneas, permite analizar el número de visitantes en cada día del mes de febrero, pero si seleccionamos dos elementos en el slicer nos permitirá hacer comparaciones entre atracciones.
El segundo gráfico, nos permite comparar el número total de visitantes para cada una de las atracciones.
Sin embargo, como es un gráfico dinámico, podemos filtrar ciertas atracciones para hacer más detallados en la comparación.
Y bueno, de esta manera le damos la posibilidad al usuario de visualizar las cifras desde diferentes perspectivas.
Nota: Para crear cálculos más avanzados en tablas dinámicas es esencial el manejo de Power Pivot por lo que te recomiendo que el siguiente contenido: Nociones en Power Pivot
Adicionalmente, si quieres explorar un poco más sobre el poder que tiene Power Query para limpiar datos te recomiendo explorar los diferentes articulos de EFB, sobre este tema,
Para finalizar, quiero contarte que con Power Query podemos automatizar todo el proceso de ETL, pero próximamente profundizaremos en ello, por hoy de mi parte es todo, un abrazo y hasta la próxima ocasión.