Explorando Power Query

Hola

Si eres un fiel usuario de Microsoft Excel, que prácticamente, todos los días en la oficina realizas la misma tarea de arreglar los datos provenientes de un archivo Excel, un archivo plano (csv, txt, etc.) o una conexión de base de datos, sea para enviarle un reporte a tu gerente u otra tarea similar, este artículo te ayudará a ahorrar mucho tiempo.

Ahora, nos hacemos un par preguntas: ¿Esto agrega valor al negocio? ¿Realmente es necesario repetir el mismo proceso diariamente?, la respuesta es NO, ya que Excel cuenta con una extraordinaria herramienta self service llamada Power Query.

¿Qué es Power Query?

Power Query es una tecnología ETL (extract, transform, load) que brinda al usuario final una experiencia intuitiva que nos permite manipular y limpiar nuestros datos conectándonos a una amplia variedad de orígenes que finalmente cargaremos en Excel, Power BI o en un modelo de datos (Power Pivot).

¿Qué nos brinda Power Query?

Power Query cuenta con muchas funciones que nos van a ayudar a manipular nuestros datos. A continuación, se listan algunas de sus principales características:

  • Conectar datos desde una gran variedad de orígenes.
  • Importar y combinar datos desde varios orígenes.
  • Crear vistas personalizadas de los datos.
  • Limpiar tus datos.
  • Usar el lenguaje M.

¿Cómo funciona Power Query?

Power Query es fácil de aprender a comparación de otras tecnologías como VBA (macros), ya que cuenta con una gran cantidad de herramientas de manipulación de datos a través de su funcionalidad de arrastrar y soltar, y que puede resultar ser una simple conversión de un texto a entero o una compleja tarea de “pivotear” una tabla.

Todos los pasos que se van realizando al manipular los datos de una conexión en particular, se van guardando uno por uno, y todo este conjunto de pasos es denominado como una consulta (query). Esta consulta puede ser reutilizada para nuevas conexiones que cuenten con la misma estructura de datos que se utilizó para crear la consulta.

Actualmente Power Query se encuentra en su versión 2.49 pero desde la versión 2.10 las consultas de Power Query pueden ser compartidas con otros usuarios.

https://image.slidesharecdn.com/self-servicedataintegrationwithpowerquery-150220080231-conversion-gate02/95/selfservice-data-integration-with-power-query-6-638.jpg?cb=1424420790

Fréchette, S. (2015). Ilustración de “Self-Service Data Integration with Power Query”.

Primeros Pasos con Power Query

En esta oportunidad nos conectaremos a un archivo plano (Empleados.csv) con columnas separados por comas, que contiene datos de diferentes tipos, como vemos en la siguiente figura.

El objetivo de esta demostración es manipular los datos para que sean cargados en un archivo Excel con la siguiente estructura:

La versión de Microsoft Excel utilizada para el ejemplo es Excel 2016.

1. Nueva Consulta

  • Para comenzar, debemos abrir un nuevo libro de Excel, nos ubicamos en la pestaña Datos y hacemos clic en la opción Nueva Consulta / Desde un archivo / Desde un archivo CSV

  • Nos mostrará un cuadro de diálogo donde ubicaremos la ruta donde se encuentra el archivo Empleados.csv y daremos clic en el botón Importar.

 

2. Editar Consultas

En el siguiente cuadro daremos clic en el botón Editar y nos mostrará el Editor de Consultas (Query Editor)

3. Usar la primera fila como encabezado

Se mostrará una ventana en la cual daremos clic en el botón Usar la primera fila como encabezado, porque la primera fila de nuestro archivo .csv trae los encabezados de las columnas.

 

4. Combinar columnas

Para nuestro ejemplo debemos combinar los campos que contienen el nombre y el apellido de nuestros empleados. Para realizar esto, dejar presionada la tecla Shift y hacer clic en los encabezados de las columnas FirstName y LastName para seleccionar ambas columnas, después ir las opciones del menú Transformar y hacer clic en el botón Combinar Columnas. Posteriormente seleccionar el separador de las columnas, en este ejemplo un espacio, escribe el nombre de la nueva columna y finalmente dar clic en el botón Aceptar.

5. Reemplazar valores y convertir a tipo Fecha

  • En este paso eliminaremos el apóstrofe inicial que llegó en la columna BirthDate, reemplazándolo con un valor vacío. Para esto debemos dar clic derecho sobre el encabezado de la columna y elegir la opción Reemplazar los valores. Finalmente dar clic en el botón Aceptar.

  • Nuevamente seleccionamos la columna BirthDate. Hacemos clic derecho, elegimos la opción Cambiar tipo / Fecha, y nuestra columna quedará con el formato fecha. Hay que tener en cuenta que un campo así luzca como una fecha, eso no te asegura que lo sea para Excel.

6. Cambiar textos a minúsculas

En este paso cambiaremos el texto de la columna EmailAdress a minúsculas. Para esto debemos dar clic derecho en el encabezado de la columna y elegir la opción Transformar / minúsculas.

7. Dividir y eliminar columnas.

En este paso necesitamos quedarnos sólo con el nombre del departamento de la columna Departament y eliminar el número inicial.

  • Primero debemos seleccionar la columna Departament, ir al menú Inicio y elegir la opción Dividir columnas / Por Delimitador, seleccionar el delimitador (en este caso el guión), seleccionar la opción Cada aparición del delimitador y finalmente dar clic en Aceptar.

  • Ahora se han creado dos columnas Department.1 que contiene los números y la columna Departament.2 que contiene sólo los nombres de los departamentos. Para terminar este paso debemos dar clic derecho sobre el encabezado de la columna Departament.1 y elegir la opción Quitar.

8. Guardar la consulta y cargar la vista en Excel.

  • Para finalizar este tutorial debemos cargar una vista con todos nuestros cambios en un libro de Excel. Para esto primero cambiamos los nombres de los encabezados y damos clic en el botón Cerrar y Cargar.

  • Y finalmente resultará un libro de Excel con las columnas que requeríamos el inicio.

Antes de despedirme, seguramente alguien se preguntará: ¿Esto lo podemos hacer en Excel tradicional?

Después de haber visto cuáles son algunas de las características que tiene Power Query podemos darnos cuenta que es una herramienta que tiene mucho por explotar.

Aún queda mucho por explorar y será hasta una nueva ocasión en que pueda mostrarles un poco más de esta grandiosa herramienta… ¡Hasta luego!

Ya sea a través de nuestros servicios de consultoría o de un entrenamiento para ti y tu organización, en Kaits Consulting tenemos un equipo de consultores especializados en tecnologías alrededor de los datos que puede ayudarte a agilizar la puesta en marcha de soluciones que pongan a trabajar los datos para ti. Solicita una demostración, con gusto te atenderemos.