Hola Estimad@ Lector
Para darte una idea, Power Query también pertenece a la artillería pesada en nuestro arsenal de Excel; para limpiar, integrar, estandarizar y transformar datos, que pueden ser utilizados en reportes u otro tipo de soluciones creadas directamente en Excel o con ayuda de Power Pivot.
Todo el proceso de Importar, Transformar y Cargar datos (Proceso ETL -Extract Transform and Load-) lo podemos realizar eficientemente con Power Query, sin tener que apoyarnos en VBA ni tampoco funciones matriciales u otro tipo de habilidades que, para ser honesto requieren de mucha práctica y únicamente pocos usuarios dominan a la perfección, pero veamos de qué se trata a través del siguiente ejemplo.
– Ejemplo: Reporte de Ingresos 2007 – 2015
Nuestra misión, es crear un reporte sobre los ingresos obtenidos en los diferentes años de la compañía, para realizar esta labor, se nos ha proporcionado el siguiente archivo de texto:
Como podemos ver, el archivo de texto no sigue ciertos estándares (Formato tabular para que te des una idea) ya que la primera fila contiene información que no es relevante para el análisis, adicional a ello, lo entorpece. En consecuencia es necesario hacer ciertas modificaciones…
– Solución
1. Importar a Power Query (Extract) Datos desde el archivo de Texto
Primero, abrimos un nuevo libro en Excel y nos dirigimos a la pestaña Power Query, allí, en el grupo Obtener Datos Externos damos clic en el comando Desde un Archivo y seleccionamos la opción Desde Texto.
Nota: Para la Versión de Excel 2013 Power Query es un complemento y no viene incorporado en Excel, debe ser descargado, instalado y habilitado en complementos COM. Lo anterior difiere con la versión de Excel 2016, debido a que Power Query pasa a ser una funcionalidad nativa de Excel y se encuentra en la pestaña DATOS, grupo OBTENER Y TRANSFORMAR.
A continuación, elegimos la ubicación del archivo de texto en nuestro computador y presionamos Aceptar.
Automáticamente, aparece el Editor de consultas o la interfaz propia de Power Query con la tabla cargada y con ciertas modificaciones hechas.
Editor de Consultas: aquí limpiamos y transformamos la tabla de datos (Que en esencia es una copia de los datos de origen, creada únicamente para facilitar el análisis, por eso las modificaciones hechas solo se ven reflejadas en la tabla de salida y no directamente en el origen de los datos).
Nota: El nombre de la consulta por defecto es el mismo del nombre del archivo de origen; es una muy buena práctica darle un nombre descriptivo.
Power Query «intuye» que es un tipo de archivo de texto delimitado por caracteres, en específico, las columnas están demarcadas por punto y coma (;), sin embargo, la primera fila y la última columna no son necesarias así que debemos modificar nuestra Consulta.
Nota: en el panel Configuración de la Consulta podemos modificar el nombre de la consulta o tabla de salida que queremos obtener a través de Power Query, así, como los pasos o modificaciones aplicados a la tabla inicial o de origen.
2. Transformación en el Editor de Power Query (Tranform)
Primero, vamos a eliminar la última columna, dando clic izquierdo sobre su etiqueta para seleccionarla, luego, clic derecho y en la lista de opciones que aparece elegimos Quitar.
Ahora, vamos a dirigirnos a la pestaña Inicio en el Editor de Consultas y damos clic en el comando Reducir Filas y posteriormente Quitar Filas Superiores.
En el cuadro de diálogo que aparece, especificamos el número de filas superiores que deseamos quitar, para este caso la primera fila y damos clic en Aceptar.
Adicionalmente, vamos a la pestaña Transformar y en el grupo Tabla damos clic en el comando Usar la Primera Fila Como Encabezado y elegimos la opción del mismo nombre.
Para finalizar la transformación de los datos, vamos a modificar el Tipo de formato de la columna Ingresos y la Columna Gastos (Manteniendo CTRL presionado, elegimos las etiquetas de la columna Ingreso y Gastos con clic izquierdo -> clic derecho sobre alguna columna -> seleccionamos la opción Cambiar Tipo -> Número Entero).
Y eso es todo lo que debemos hacer para limpiar nuestros datos, observemos como la tabla ha sido modificada y lista para ser cargada:
Pausemos un momento y veamos como en el área de Pasos Aplicados quedan guardados los pasos de modificación que se han hecho en la tabla, para que al cargar nuevos datos en el origen, no se deba repetir el proceso de transformación sino que solo sea necesario actualizar la consulta… algo increíblemente Productivo, solo piensa en esto ¿ Cuánto tiempo gastamos al mes o a la semana limpiando y preparando datos?
3. Cargar (Load)
Para cargar la tabla en el modelo de datos, nos dirigimos al comando Cerrar y Cargar y elegimos la opción Cerrar y Cargar En.
En el cuadro de dialogo Cargar En, elegimos donde queremos cargar la tabla limpia y lista para ser utilizada, ya sea directamente al modelo de datos («Power Pivot») o como una Tabla Estructurada en Excel, para este caso la primera opción.
Por eso debemos habilitar las opciones Crear solo conexión y Agregar Estos al Modelo de Datos, para finalizar damos clic en Aceptar y así de fácil cargamos la consulta en el Modelo de Datos.
4. Crear Reporte Tabla Dinámica
Para resumir la información y poder analizarla, vamos a crear un reporte de tabla dinámica utilizando la Consulta que recién hemos creado: Pestaña Insertar –> Grupo Tablas -> Tabla Dinámica -> en el cuadro de dialogo Crear Tabla Dinámica habilitamos la opción -> Utilice una fuente de datos Externa -> Damos clic en el Botón Elegir Conexión.
Al presionar el botón Elegir Conexión aparece el cuadro de dialogo Conexiones Existentes donde debemos elegir el nombre de la consulta y presionamos el botón Aceptar.
Nuevamente damos Aceptar en el cuadro de Dialogo Crear Tabla Dinámica y ahora aparece el área para construir una tabla dinámica junto con el panel de consultas y el panel de campos de una tabla dinámica.
Ahora vamos a mover al área de filtros, el campo Año, al área de filas el campo Mes, y al área de valores los campos Ingresos y costos.
Para hacer el reporte más robusto crearemos una Medida en Power Pivot que me determine el promedio de ingresos.
Seleccionamos la pestaña Power Pivot de Excel, grupo Cálculos y en la lista Campos Calculados elegimos la opción Nuevo Campo Calculado.
En el cuadro de dialogo Campo Calculado vamos a crear la medida:
=AVERAGE(Ingresos[Ingresos])
Comprobamos la formula y damos clic en Aceptar…
En el panel de campos de nuestra tabla dinámica aparece la nueva medida, adicionalmente, vamos a agregarla al área de valores.
Y de esta forma hemos finalizamos la creación del reporte, evitando que se convierta en un dolor de cabeza la transformación y preparación de los datos, adicionalmente, observa como Power Pivot trabaja en armonía con Power Query,¡una pareja explosiva! -y hay más 😉 –
Por el día de hoy eso es todo, hasta la próxima publicación.
Un Abrazo, su amigo y servidor