¡Excel PowerQuery Hack! Conexiones con rutas relativas en 10 minutos!

¡Excel PowerQuery Hack! Conexiones con rutas relativas en 10 minutos!

¿Harto de ajustar las conexiones en PowerQuery cada vez que compartes tu archivo de Excel? 🙄 Convierte las conexiones de PowerQuery con rutas relativas que se ajustan a la posición del fichero de Excel.

Descubre cómo usar la función Celda y el poder de los rangos con nombre para actualizar los orígenes automáticamente. 🤯 En este video, te guiaré paso a paso a través del proceso para que nunca más pierdas tiempo con ajustes manuales. ¡Aplica este truco y potencia tus habilidades de PowerQuery al máximo!

00:00 Introducción al problema de rutas fijas en Power Query
00:57 Crear una nueva carpeta y mover ficheros para cambiar ruta
01:18 Error al actualizar los datos por ruta fija
01:26 Usar función CELDA para obtener ruta dinámica
01:56 Nombrar la celda con la ruta para referencia en Power Query
02:16 Editar conexión en Power Query con ruta dinámica
03:15 Actualizar datos con ruta modificada sin error
03:22 Cambiar nombre del fichero y actualizar datos exitosamente
04:05 Resumen del proceso y cómo mantener relaciones de directorio

No olvides suscribirte para más contenido y técnicas que te harán un maestro de Excel. 👇 Dale a la campanita para no perderte ningún truco.

Uno de los problemas más engorrosos de Power Query aparece cuando mueves un fichero de carpeta o se lo pasas a otro compañero para que lo abra en su ordenador: de repente todas las conexiones dejan de funcionar. El motivo es que, en el momento en que creas la conexión, Power Query graba una ruta fija hasta el archivo de origen. Si esa ruta cambia, la actualización falla. En este tutorial veremos cómo convertir esa ruta fija en una ruta dinámica que se adapte automáticamente a la posición real de los ficheros.

El problema de las rutas fijas

Para el ejemplo trabajamos con dos archivos muy sencillos colocados uno al lado del otro:

  • El integrador, que contiene una consulta de Power Query.
  • El fichero de origen, que es el que la consulta va a buscar.

Si abrimos la consulta del integrador, vemos que el origen apunta a la ruta completa, desde la unidad hasta el nombre del fichero. Mientras todo permanece en su sitio, al actualizar funciona a la perfección.

El problema surge al mover los archivos. Si creamos una carpeta nueva (por ejemplo, ejemplo) y trasladamos allí ambos ficheros, acabamos de cambiar la ruta del origen. Al pulsar Actualizar, Power Query muestra el mensaje de que no pudo encontrar el fichero, porque la ruta quedó grabada de forma fija en el momento de crear la conexión.

Crear una celda con la ruta dinámica

La solución consiste en calcular la ruta del libro desde una celda de la propia hoja y pasársela a Power Query. Para ello:

  1. Abrimos una hoja nueva que usaremos solo para este propósito.
  2. Escribimos la función CELDA incluyendo el parámetro "nombre archivo", que devuelve la ruta completa de donde se encuentra el fichero abierto.
  3. Como esa ruta incluye también el nombre del libro entre corchetes, usamos la función IZQUIERDA para extraer únicamente la primera parte de la ruta, hasta la barra que cierra el directorio.

Al combinar ambas funciones obtenemos la ruta de la carpeta terminando en la barra, justo antes del nombre del fichero. Es la parte que necesitamos como referencia.

Nombrar la celda

Para poder referenciar fácilmente ese valor, le damos un nombre de rango a la celda. En el ejemplo la llamamos ruta. Con esto ya tenemos generada nuestra variable, lista para incluirla en Power Query.

Sustituir la ruta fija en Power Query

Ahora llevamos esa variable a la consulta:

  1. Vamos a la consulta de conexiones y editamos la conexión que está dando error (la que tenía la ruta antigua y no encuentra el directorio).
  2. En el paso de origen, sustituimos toda la primera parte de la ruta (la que llega hasta el nombre del fichero de origen) por una referencia dinámica.

La idea es hacer referencia al libro actualmente abierto, buscar el nombre que acabamos de generar (ruta) y recuperar su valor: la primera fila de la primera columna de ese rango con nombre. Ese valor es la ruta de la carpeta que necesitamos.

Al validar y pasar al siguiente paso, Power Query vuelve a reconocer la ruta completa y recupera correctamente los datos de origen. Cerramos el editor y, al pulsar Actualizar, ya no aparece ningún error: la información se carga sin problemas.

Probar que funciona

Para comprobar que la solución es robusta, podemos forzar de nuevo el error. Esta vez, en lugar de mover los ficheros de sitio, simplemente cambiamos el nombre del directorio que los contiene. Es el equivalente práctico a pasárselo a otra persona en su ordenador, donde las rutas serán distintas, o a hacernos una copia para realizar pruebas sin tener que reconstruir toda la estructura de enlaces.

Tras renombrar el directorio, volvemos a entrar en el integrador y pulsamos Actualizar. La consulta reconoce correctamente el origen y muestra los datos de nuevo, sin miedo a que falle.

Casos de uso

Esta técnica resulta muy útil cuando:

  • Reorganizas tus carpetas y mueves todos los ficheros relacionados con Power Query a otra ubicación.
  • Compartes el libro con otra persona que lo abrirá desde rutas diferentes en su equipo.
  • Haces varias copias del proyecto para test o pruebas, sin tener que tocar los enlaces.

La clave es mantener la posición relativa: mientras los ficheros estén en el mismo directorio (o en directorios que nacen a partir de ese), la relación se conserva. Funciona incluso al cambiar de ordenador.

Conclusión

En resumen, el truco es sencillo: habilitamos una celda donde extraemos la ruta del fichero con CELDA e IZQUIERDA, le asignamos un nombre de rango y, mediante la referencia al libro actual en Power Query, inyectamos ese valor en la parte del origen de la conexión. Así dejamos atrás las rutas fijas y conseguimos conexiones con rutas relativas que se adaptan solas, ahorrándonos rehacer el trabajo cada vez que movemos o compartimos los archivos.

Más contenido de Excel en InflueXcel