Hoy quiero explicarte qué es una herramienta ETL que, así de sopetón te puede sonar a klingon, pero es algo muy potente que nos facilita enormemente la vida cuando tenemos orígenes de datos raros o diferentes.
Las siglas ETL provienen del inglés, como casi todas, y corresponden a los términos EXTRAER (extract), TRANSFORMAR (transform) Y CARGAR (load).
Hay múltiples herramientas ETL, pero yo te quiero hablar de Power Query que es la herramienta ETL de Excel.
Como usuarios de Excel, seguro que nos hemos fijado que en la cinta de opciones hay una pestaña que se llama “Datos”, pero la mayoría nos quedamos en los botones que hay en las fichas “Ordenar y filtrar” y, como mucho, en “Herramientas de datos”:
Bueno, pues hoy hablaré de las fichas “Obtener datos externos” y “Obtener y transformar datos”.
Contenidos
Las fichas ETL en Excel
Estas dos fichas, en el fondo, son la misma.
La primera corresponde a versiones de Excel más antiguas (hasta 2013 creo, pero con los cacaos que lleva Excel con sus versiones, igual te digo esto y estoy metiendo la pata) y la segunda a versiones más actualizadas (creo que a partir de la 2016).
Quiero decirte que, por defecto, sólo vas a tener una de las dos fichas. No te creas que es raro no tenerlas las dos.
Yo las tengo activadas las dos, porque te quiero enseñar las dos versiones, pero lo normal es tener solo una de ellas.
Si quisieras activar la otra (pero ya te digo que no es necesario), sólo lo podrás hacer en caso de que tu versión de Excel sea superior a la 2016.
Te muestro cómo hacerlo.
Activar y desactivar Las fichas ETL en Excel
Para activar o desactivar estas fichas, vas a tener que ir a la pestaña “Archivo”, de ahí a “Opciones” y a la opción “Personalizar cinta de opciones”.
En el desplegable “Comandos disponibles en:” tienes que escoger la opción “Todas las pestañas” y, de las “Pestañas principales”, desplegar las fichas que corresponden a la pestaña “Datos”.
Ahí puedes agregar o quitar la que quieras, pero no te aconsejo que quites la opción de “Obtener y transformar datos” porque es la más actual y potente.
Aceptas y ya lo tienes.
Una vez activadas las dos fichas, tienes que ver algo parecido a esto:
Ficha “Obtener datos externos”
Si tienes esta ficha, es probable que sea porque tu versión de Excel es antigua.
Al clicar sobre ella verás desde qué tipo de archivos vas a poder importar datos:
Aunque no son tantas opciones como nos ofrece la versión más actualizada, en la mayoría de los casos, son suficientes.
Las tres más usuales son las que corresponden a Access, Web y Archivo de texto, que es el equivalente a un archivo CSV.
Ficha “Obtener y transformar datos”
Ahora vamos a ver las opciones de esta ficha que, como ya te he comentado, corresponde a las versiones más actuales de Office y vas a ver que hay muchísimas más opciones:
A ver…
Sería muy osado por mi parte decir que más de la mitad de lo que hay aquí no se usa nunca, pero sí te puedo decir que, en los años que llevo usando esta herramienta ETL de Excel, he utilizado menos de la cuarta parte de lo que ves…
Por eso, yo siempre digo que es igual de dónde provengan los datos, ¡¡Excel puede con todo!!
Para qué sirven las herramientas ETL
Como muy bien dicen sus siglas, las herramientas ETL sirven para extraer, transformar y cargar datos, en este caso, en un libro de Excel.
Como que ponerme aquí a explicar cuestiones técnicas sería aburridísimo y si estás leyendo esto es porque quieres entenderlo, soy de la opinión que la mejor manera de hacerlo es a través de un ejemplo.
Y, aunque las opciones como has visto son múltiples, voy a hacerlo con una muy sencillita.
Usaré una única base de datos y haré una transformación muy básica.
Al fin y al cabo, para que entiendas qué significa trabajar con estas herramientas ETL, es más que suficiente.
¡Vamos a por el ejemplo!
Imagínate que tienes un canal de YouTube.
Cada mes recibes el informe en el que puedes ver la cantidad de reproducciones, me gustas, suscriptores ganados, suscriptores perdidos, bla, bla, bla…
Este informe lo recibes en un documento con formato CSV.
Algo parecido a esto:
Si quieres analizar de la evolución de tu canal mes a mes, aquí tienes, por un lado, unas métricas que no vas a utilizar (es más que probable que no te interesen todas las que YouTube te envía) y por otro un archivo muy poco amigable para realizar este análisis.
Por norma general, estos análisis los vas a llevar a tablas y/o gráficos dinámicos que te van mostrando la evolución con el paso del tiempo.
Pues para eso sirven estas herramientas, para permitirnos extraer los datos que nos interesan de una base de datos, escoger sólo lo que necesitamos para nuestro análisis, transformar los datos que queramos transformar y cargar esos datos en nuestra tabla dinámica, en nuestro gráfico dinámico o en nuestro cuadro de mandos.
Cómo extraer datos de un archivo CSV
A través de la opción que nos facilita Excel, le decimos que queremos extraer los datos de nuestro archivo CSV.
Buscamos la ubicación en nuestro ordenador del archivo que queremos analizar y lo cargamos.
Una vez hacemos la carga, Excel nos va a mostrar una vista previa de los datos que tiene nuestro archivo.
Si, como en este caso, el archivo está bien delimitado Excel va a entenderlo y podemos ir directamente a la transformación con la herramienta ETL que tiene Excel, que se llama Power Query.
Si no es así, antes de seguir adelante, tendremos modificarlos para que Power Query reciba los datos de la mejor manera posible.
En esta vista previa tenemos tres parámetros que podemos modificar, el origen del archivo (que es el estándar de codificación de caracteres), el delimitador (el carácter que separa cada uno de los valores) y la detección del tipo de datos (que nos dice en qué se ha basado para la detección de los datos que, por defecto, son las 200 primeras filas).
En este caso, como esos parámetros han estado bien captados, podemos pasar a la transformación de los datos a través del botón “Transformar datos”.
Cómo transformar los datos de un archivo CSV con Power Query
Llegamos a la ventana de transformación. Al corazón de Power Query…
Esta herramienta es una de las más potentes que te puedas llegar a imaginar.
Fliparás con la cantidad de opciones que te vas a encontrar.
Evidentemente, no te puedo explicar en un post todo lo que contiene, porque no sería un post, sería un libro o una formación completa.
Lo que sí te voy a explicar son los pasos que voy a hacer para conseguir analizar este archivo y obtener las métricas que me interesan.
Elegir columnas
Lo primero que quiero hacer es escoger con qué columnas me quiero quedar.
Para ello, clico sobre el botón “Elegir columnas” y selecciono aquellas que quiero llevar a mi análisis.
En este caso, por ejemplo, me voy a quedar con las columnas fecha, suscriptores, suscriptores ganados, suscriptores perdidos, me gusta, me disgusta y reproducciones.
Cambiar tipo de datos
En segundo lugar, le voy a cambiar el tipo de datos que corresponde a cada columna.
Como puedes ver en la imagen, todos los datos están alineados a la izquierda. Además, al lado del nombre de la columna, hay un icono que pone “ABC”, que quiere decir que es un valor de tipo texto.
Cuando Excel alinea a la izquierda de una celda cualquier dato, significa que para él este dato es un texto, al margen de lo que nosotros veamos.
El hecho de que los datos numéricos y las fechas las entienda como texto, puede ser por tres motivos:
1º Porque Excel no reconozca el tipo de dato.
2º Porque hay espacios en las celdas que no vemos, pero que están ahí.
3º Porque hayamos decidido que no nos haya gustado la elección del tipo de dato que ha entendido y la queramos poner de forma manual.
Esto lo solemos hacer cuando nos encontramos con valores decimales en formato anglosajón, donde la separación del decimal es un punto en lugar de la coma y si aceptamos la detección automática de Excel, los valores que nos devuelve no son correctos.
Por ejemplo, si en el archivo original tenemos un 25.18 en formato anglosajón, cuando Excel lo importe, lo cambiará por un 2518, que no…, que no…, que nos escogorcia todas las métricas…
Bueno, a lo que iba…
Hay distintas maneras de cambiar el tipo de dato, pero la más sencilla es clicando sobre el icono que encontramos en el encabezado de la columna y escogiendo de la lista desplegable de qué tipo son los datos que contiene esa columna:
Cambiar nombres a las columnas
Cambiar los nombres a las columnas es muy sencillo.
Lo podemos hacer simplemente haciendo un doble clic sobre el título y escribiendo el nombre que queramos adjudicarle a esa columna.
Añadir la columna “Año”
Añadir columnas personalizadas es otra de las opciones que tenemos en Power Query.
Para el análisis que quiero hacer, como quiero segmentarlo por años, me va a venir bien tener una columna con ese dato.
Vamos a añadir esta columna en la que, a partir de la fecha, nos devuelva únicamente el año.
Con la columna de la fecha seleccionada, voy a la pestaña “Agregar columna” y seleccionando la opción “Fecha” elijo “Año” y, de nuevo, “Año”.
Automáticamente me aparecerá al final de toda la tabla esa nueva columna con el año.
Esta columna la voy a arrastrar para situarla al lado de la fecha, que es donde quiero que esté.
Añadir columna “Nombre del mes”
De la misma manera que ha hecho con los años, Power Query puede extraer el nombre del mes de una columna con fechas.
El sistema para hacerlo es el mismo que el anterior.
Quitar la columna «Fecha»
Como ya no voy a utilizarla, voy a quitar la columna con las fechas.
Este paso lo podría haber hecho antes, es cierto, pero como este documento tenía muchas columnas, para evitar estar media hora arrastrando las columnas que he añadido para trasladarlas al principio de la tabla, he preferido dejarlo para ahora.
Como en este caso sólo voy a quitar una columna y no tengo que escoger de una lista larguísima como he hecho antes y, como todavía la tengo seleccionada del paso anterior, lo único que voy a hacer es volver a la pestaña “Inicio” y clicar en el botón “Quitar columnas”.
Con todos estos pasos, ya he conseguido hacer la transformación que necesitaba para el análisis de este canal de YouTube.
Este archivo, contiene los datos correspondientes a varios años y, como no he hecho ningún análisis del canal hasta ahora, voy a cargar todos los datos.
Si quisiera cargar únicamente los de un periodo determinado, también podría hacerlo.
Ahora paso a la tercera fase del proceso, la carga de los datos.
Cómo cargar los datos de un archivo transformado en Power Query
La carga de datos se puede hacer de muchas maneras y algunas de ellas también van a depender de la versión de Office que estemos utilizando. Las versiones más antiguas, lógicamente, están más limitadas.
De nuevo, no te puedo explicar todas las posibilidades que hay, porque son muchas y algunas un poco complejas, pero te voy a mostrar la más útil para un caso como este, que se trata de cargar los datos directamente en una tabla dinámica.
Si tu versión de Excel es anterior a la del 2016, esto no lo vas a poder hacer. En ese caso, tendrás que cargar los datos en una tabla (si éstos tienen menos de 1.048.576 filas, que es la capacidad máxima de las hojas de Excel) y a partir de esta tabla crear la tabla dinámica o si no, crear una conexión (si tienes más de ese millón y pico de datos) y generar la tabla dinámica a partir de ella.
Ya te digo que esto es un poco lioso, pero mi intención es explicarte qué significa tener una herramienta ETL para analizar datos y no explicarte cómo funciona Power Query, que esto da para una novela…
¡Venga! ¡Vamos a por la carga de datos!
En la pestaña “Inicio”, cuando selecciono la opción “Cerrar y cargar en…” me aparecen las opciones de importación que me permite mi versión de Excel.
Selecciono que quiero hacerlo en un “Informe de tabla dinámica”.
Como puedes ver, las opciones de importación (insisto, para mi versión de Excel) son:
- Enviar los datos ya transformados a una tabla
- Generar directamente la tabla dinámica
- Crear un gráfico dinámico o
- Crear solo la conexión
Además, Excel me pregunta dónde quiero situar los datos, si en una hoja existente o en una nueva y, hay otra opción (de la que no te voy a hablar porque se complicaría demasiado la cosa) que es la de agregar los datos a un modelo de datos.
Bueno, una vez escogidas las opciones, acepto y puedo ver dos cosas:
La consulta a los datos de origen que he creado, que me dice cuántas filas de datos he cargado y el área para crear la tabla dinámica:
A partir de aquí, ya solo queda generar las tablas y los gráficos que queramos con los datos que hemos importado:
Además, hay una cosa más que no he explicado todavía.
Lo más bestia de Power Query es que este trabajo ya no tendré que volver a hacerlo nunca más (a no ser que cambie el formato de los informes, claro).
A partir de ahora, cuando mes a mes me descargue estos informes del canal de YouTube, sólo tendré que decirle a Excel que recoja los nuevos datos y los añada a los que tengo hasta ahora y él solito se va a encargar de realizar todo el proceso de forma automática.
Bueno, y hasta aquí ha llegado el rollo de hoy.
Espero que este ejemplo te haya servido para entender un poco qué son las herramientas ETL y para darte cuenta de la potencia que tienen.
