Si Power Query hubiese existido hace 20 años...

Este es el primer artículo que escribo sobre Power Query. Quiero hacerlo con la finalidad de preguntarte algo cuando termines de leerlo: ¿podrías implementar Power Query en tu trabajo actual?

Deja que te cuente la historia de cómo podría haber sido mi trabajo en una empresa si hubiese existido Power Query hace 20 años.

El tedioso tratamiento de datos

En alguna ocasión ya he hablado acerca de uno de los primeros trabajos que tuve en una central de compras de ferretería. Yo era el responsable de los ficheros maestros de la compañía y mi gran tarea era mantener los datos de los productos actualizados. Al comienzo contábamos con unos 25.000 productos, pero queríamos llegar a 250.000 en tres años.

Para aumentar el número de productos, se negociaba con nuevos proveedores que debían enviarnos sus catálogos en formato digital para que yo los fuese incorporando a nuestra base de datos.

¡Hasta ese momento no sabía que existían tantos tipos de archivos!

Me enviaban ficheros en csv, xml, archivos de Excel, archivos de texto txt, incluso html… En el mismo formato que a ellos les salía de su ERP, a mí me lo enviaban sin hacer comprobaciones. Te podrás imaginar qué caos era aquello, ¿verdad?

Cada vez que recibía una tarifa, tenía que seguir los mismos pasos:

• Importaba cada archivo en Excel. Formatos como csv o xml son fáciles de importar, pero otros como pdf, por ejemplo, no lo son tanto. (de 2 a 30 minutos).
• Ponía la información en el orden adecuado. Según el formato del archivo tenía que aplicar más o menos pasos, pero en la totalidad de ellos había que hacer algún tipo de tarea. Ten en cuenta que cada ERP cuenta con sus estándares para manejar información. (entre 5 min. y 2 horas dependiendo del número de productos y de transformaciones que hubiera que hacer).
• Agregaba los datos que faltaban. Los proveedores enviaban sus tarifas con un campo que denominábamos “familia” y que servía para aplicar descuentos. A cada familia le correspondía un porcentaje de descuento y yo debía rellenar estos datos. (Entre 10 y30 min.).
• Exportaba el resultado al formato csv que nuestro software era capaz de leer. (1 min.).
• Importaba el csv en el ERP. (30 segundos).

Resumiendo, cada vez que tenía que trabajar sobre una tarifa tardaba entre 15 minutos y varias horas desde que comenzaba hasta que terminaba importando los datos en nuestro software.

No recuerdo el número exacto de proveedores con los que trabajábamos pero sí eran alrededor de 250.

¡Y no solo eso! Luego estaban los cambios. Cuando un proveedor actualizaba sus precios, que normalmente lo solían hacer una vez al año, nos debían enviar de nuevo la tarifa y yo tenía que comenzar de nuevo el proceso.

¿Entiendes lo que quiero decir? La preparación de información se puede volver una tarea complicada si no tenemos una gran organización del trabajo.

Así eran todos mis días en aquel trabajo (año 1999, creo). Si tuviese que volver a desempeñar las mismas funciones, estoy seguro de que ya no serían igual, gracias a la aparición de Power Query.

¿Qué es Power Query?

Power Query es una herramienta (o complemento, según la versión de Excel que utilices) para extraer información de múltiples fuentes de datos, transformar esa información y cargarla en Excel o en el modelo de datos. Se trata de una herramienta de ETL (Extract, Transform and Load).

Si la versión de Excel que tienes es 2010 o 2013, Power Query no está incorporado en el programa, por lo que deberás descargarte el complemento gratuito desde aquí:

Si tienes Excel 2016 o posteriores, Power Query ya se encuentra integrado en Excel como una herramienta más.

Atención: También es posible utilizar Power Query desde Power BI Desktop sin necesidad de tener Excel instalado. Además, desde esta versión es posible acceder a más fuentes de datos de terceros.

¿Cómo sería mi trabajo ahora utilizando Power Query?

Ya has visto que cuando trabajaba con los ficheros maestros no tenía tiempo de aburrirme, ????. Bueno, me sobraba algo de tiempo que utilizaba en intentar crear alguna macro que me facilitase alguno de los procesos.

Si en estos momentos tuviese que volver a esa empresa, mi forma de trabajar sería totalmente diferente. Te lo muestro en un ejemplo. Se trata de la empresa Meichar (es fecticia), que actualiza sus tarifas cada seis meses:

1. Le pido a Meichar que me envíe su tarifa actualizada y me comentan que la única forma que tienen de envíarmela es en un archivo txt. "Bueno – les digo – enviádmela como sea…".
2. Una vez recibida veo que no tiene muy buena pinta, pero me da igual porque cuento con Power Query, ????. Me envían dos archivos: uno en formato de texto con los productos y los precios y el otro con los descuentos por marca.

Extraer

3. Como el proveedor me envía la tarifa cada seis meses, he de trabajar con datos diferentes cada vez, por lo que decido crear una carpeta donde alojar tanto el archivo recibido con el archivo de Excel que voy a utilizar para tratar la información.

4. Abro el archivo de Excel (que en este momento está vacío), me dirijo a la ficha Datos y hago clic en Obtener datos – Desde un archivo – Desde una carpeta:

5. Busco la carpeta donde se encuentra la tarifa y hago clic en Aceptar.

6. Aparecen tres registros: Dos de ellos corresponden al propio archivo de Excel y su copia temporal y el otro, el que me interesa (extensión txt) corresponde a la tarifa que quiero importar. Pulso en el botón Importar datos para tomar únicamente el archivo que necesito:

Desde aquí, desde la interfaz de Power Query me voy a quedar solamente con el archivo txt.

Transformar

7. Pulso en el botón desplegable de la columna Extensión y dejo marcado el valor txt. Pulso en Aceptar.

Ahora ya puedo trabajar con los datos que contiene el archivo. Hago clic en el botón Combinar.

En este caso he tenido suerte porque Power Query ha detectado la delimitación de las columnas y ha establecido los saltos de campo automáticamente. Con otras tarifas tendría que delimitar manualmente las columnas. Si mediante este paso no fuese capaz de hacerlo, cuando esté trabajando con Power Query sería el momento de separar las columnas. Pulso en Aceptar.

9. Entre estos datos hay columnas que no necesito (Source.txt y Refxx), por lo que las selecciono y pulso la tecla Supr.

10. Repasando el formato de datos de cada columna me doy cuenta de que el código EAN está en notación científica así que lo modifico pulsando en Inicio – Grupo Transformar – Tipo de dato. Aquí selecciono Texto.
11. Además de la tarifa, Meichar también me envía un archivo de Excel que contiene las marcas que distribuye y sus correspondientes descuentos.

Con mi antigua forma de trabajar tendría que utilizar funciones como BUSCARV para asignar el descuento a cada producto. Pero las fórmulas ocupan espacio y ralentizan los cálculos. Ahora, con Power Query, puedo llegar al mismo resultado sin necesidad de utilizar fórmulas. Voy a combinar los archivos de la forma más eficiente.

12. En la ventana de Power Query hago clic en el botón Nuevo origen en el grupo Nueva consulta de la ficha Inicio. A continuación hago clic en Archivo – Excel.

13. Selecciono el archivo de descuentos del cuadro de diálogo que se muestra. A continuación aparece el cuadro Navegación desde el que selecciono la Hoja1, que es donde se encuentran los descuentos:

14. Al hacer clic en Aceptar, se crea una nueva consulta.

15. Tras cambiar el nombre de la consulta de Hoja1 a Descuentos y seleccionar la consulta Meichar, procedo a combinarla con la que tiene los productos. Para eso hago clic en el botón Combinar consultas de la ficha Inicio:

16. Ahora toca relacionar ambas consultas desde el cuadro que ha aparecido en la pantalla. Como ves, aparece dividido. En la parte superior se muestran los campos de una de las consultas y en la parte inferior se muestra la otra cuando hago clic en el desplegable y selecciono su nombre. A continuación selecciono los campos de ambas consultas que quiero relacionar.

17. Como ves en la siguiente imagen, se ha creado una nueva columna en la que se muestra la palabra Table. Hago clic en el botón del encabezado y dejo marcado el campo Descuento. A continuación hago clic en Aceptar.

18. Como ves en la siguiente imagen, desaparece la palabra Table y se sustituye por el descuento correspondiente.

Cargar

19. Ahora ya tengo el archivo en el formato que yo quería, es hora de cargarlo a Excel para poder importarlo en la base de datos. Hago clic en la ficha Inicio y a continuación en el desplegable Cerrar y cargar – Cerrar y cargar en.

20. Elijo cargarlo en una nueva tabla.

21. ¡Y listo!

En seis meses Meichar volverá a enviarme su tarifa con los precios actualizados. El archivo tendrá artículos nuevos, otros los habrán dado de baja, algunos precios habrán cambiado… ¿Qué haré con el archivo? Lo pondré en la misma carpeta donde estaba la anterior tarifa y ejecutaré la consulta. En menos de un segundo, todos los pasos anteriores se vuelven a reproducir, dejando el archivo con el formato adecuando para introducirlo en mi programa.

Resumen

Si hubiese conocido antes Power Query, ¡podría haberme ahorrado el 90% del trabajo! Eso si, al principio tendría que haber creado una consulta para cada proveedor. Recuerda que me enviaban sus datos en formatos muy dispares.

¿Para qué utilizarías tú Power Query? Déjamelo en los comentarios.