5

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?

Colapso sin Power Query Excel

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.
Archivo CSV desordenado Excel

Extraer

  1. 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.
Carpeta con archivos Power Query Excel
  1. 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:
Desde una carpeta Power Query Excel
  1. Busco la carpeta donde se encuentra la tarifa y hago clic en Aceptar.
Ruta a la carpeta Power Query Excel
  1. 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:
Interfaz Excel Power Query

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

Transformar

  1. Pulso en el botón desplegable de la columna Extensión y dejo marcado el valor txt. Pulso en Aceptar.
Filtrar archivos Power Query Excel

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

Combinar archivos Power Query Excel

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.

Consulta importada Power Query Excel
  1. Entre estos datos hay columnas que no necesito (Source.txt y Refxx), por lo que las selecciono y pulso la tecla Supr.
Eliminar columnas Power Query Excel
  1. 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.
  2. 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.
Tabla Excel

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.

  1. 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.
Nuevo origen datos Power Query Excel
  1. 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:
Navegador Power Query Excel
  1. Al hacer clic en Aceptar, se crea una nueva consulta.
Nueva consulta Power Query Excel
  1. 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:
Combinar consultas Excel
  1. 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.
Combinación de consultas Power Query Excel
  1. 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.
Filtrar datos Excel Power Query
  1. Como ves en la siguiente imagen, desaparece la palabra Table y se sustituye por el descuento correspondiente.
Combinar campos Power Query

Cargar

  1. 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.
Power Query cargar en Excel
  1. Elijo cargarlo en una nueva tabla.
Cargar en tabla Excel
  1. ¡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.

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 5 comentarios
Salomón Bravo Ojeda - 01/04/2019

La idea es poder usarlo para presentación de informes contables y realización de información exogena.

Responder
juan de dios briones - 01/04/2019

Estsimado Sergio,

mira yo utilizaria power query para acelerar mi trabajo ya que tengo una base de datos de productos que tiene precio unitario, factor de rendimiento, codigo y descripcion de matarial, y tgambien precio de venta.
esa tabla me sirve para alimentar un inmenso cuadro de datos que extraigo de un sistema para equiparar todas las unidades de medida de venta a una sola unidad como ser yardas.
y el estar pegando buscarv o sumarsi hace que se ponda lento mi excel a mas no poder.

Responder
jose rafael - 01/04/2019

Gracias
Profesor Sergio Propegol por tu generosidad al enviarnos los mensajes de cambio que debemos hacer en nuestras costumbres; para utilizar nuevas técnicas que con tu ayuda se vuelven aplicables y comprensibles. Me interesa saber más sobre Power Query.

Responder
Santos - 01/04/2019

Interesante contenido, Maestro, que sigan los éxitos.

Responder
Daniel - 02/04/2019

La herramienta es funcional para agilizar un inventario ya que es de dos empresas para unificarlos en una sola

Responder

Escribe una respuesta:

Powered by WishList Member - Membership Software