Parametrización de rutas de datos en Power Query

Si ya comenzó a usar las herramientas del complemento gratuito Power Query en Microsoft Excel, muy pronto se encontrará con un problema altamente especializado, pero muy frecuente y molesto, asociado con la ruptura constante de los enlaces a los datos de origen. La esencia del problema es que si en su consulta hace referencia a archivos o carpetas externos, Power Query codifica la ruta absoluta a ellos en el texto de la consulta. Todo funciona bien en su computadora, pero si decide enviar un archivo con una solicitud a sus colegas, se sentirán decepcionados, porque. tienen una ruta diferente a los datos de origen en su computadora y nuestra consulta no funcionará.

¿Qué hacer en tal situación? Veamos este caso con más detalle con el siguiente ejemplo.

Formulación del problema

Supongamos que tenemos en la carpeta E:Informes de ventas miente el archivo Los 100 mejores productos.xls, que es una carga desde nuestra base de datos corporativa o sistema ERP (1C, SAP, etc.) Este archivo contiene información sobre los productos básicos más populares y se ve así en el interior:

Probablemente esté claro desde el principio que es casi imposible trabajar con él en Excel de esta forma: filas vacías a través de una con datos, celdas combinadas, columnas adicionales, un encabezado de varios niveles, etc. interferirán.

Por lo tanto, al lado de este archivo en la misma carpeta, creamos otro archivo nuevo Manejador.xlsx, en el que crearemos una consulta de Power Query que cargará datos feos del archivo de carga de origen Los 100 mejores productos.xls, y ponlos en orden:

Hacer una solicitud a un archivo externo

Abriendo el archivo Manejador.xlsx, seleccione en la pestaña Datos Comando Obtener datos – Desde archivo – Desde libro de Excel (Datos — Obtener datos — Desde archivo — Desde Excel), luego especifique la ubicación del archivo fuente y la hoja que necesitamos. Los datos seleccionados se cargarán en el editor de Power Query:

Vamos a traerlos de vuelta a la normalidad:

1. Eliminar líneas vacías con Inicio — Eliminar líneas — Eliminar líneas vacías (Inicio — Eliminar filas — Eliminar filas vacías).
2. Elimine las 4 líneas superiores innecesarias a través de Inicio — Eliminar filas — Eliminar filas superiores (Inicio — Eliminar filas — Eliminar filas superiores).
3. Suba la primera fila al encabezado de la tabla con el botón Usar la primera línea como encabezados de la pestaña. Inicio (Inicio: use la primera fila como encabezado).
4. Separe el artículo de cinco dígitos del nombre del producto en la segunda columna usando el comando columna dividida de la pestaña. (Transformar — Dividir columna).
5. Elimine las columnas innecesarias y cambie el nombre de los encabezados de las restantes para una mejor visibilidad.

Como resultado, deberíamos obtener la siguiente imagen, mucho más agradable:

Queda por subir esta tabla ennoblecida de nuevo a la hoja en nuestro archivo Manejador.xlsx el equipo cerrar y descargar (Inicio — Cerrar y cargar) de la pestaña. Inicio:

Encontrar la ruta a un archivo en una solicitud

Ahora veamos cómo se ve nuestra consulta "bajo el capó", en el lenguaje interno integrado en Power Query con el nombre conciso "M". Para ello, vuelve a nuestra consulta haciendo doble clic sobre ella en el panel derecho Solicitudes y conexiones y en la pestaña Revisar escoger Editor avanzado (Ver — Editor avanzado):

En la ventana que se abre, la segunda línea revela inmediatamente una ruta codificada a nuestro archivo de carga original. Si podemos reemplazar esta cadena de texto con un parámetro, variable o un enlace a una celda de hoja de Excel donde esta ruta está preescrita, entonces podemos cambiarla fácilmente más adelante.

Agregar una tabla inteligente con una ruta de archivo

Cerremos Power Query por ahora y volvamos a nuestro archivo Manejador.xlsx. Agreguemos una nueva hoja vacía y hagamos una pequeña tabla "inteligente" en ella, en la única celda en la que se escribirá la ruta completa a nuestro archivo de datos de origen:

Para crear una tabla inteligente a partir de un rango regular, puede usar el atajo de teclado Ctrl+T o botón Formatear como una tabla de la pestaña. Inicio (Inicio — Formatear como tabla). El encabezado de la columna (celda A1) puede ser absolutamente cualquier cosa. También tenga en cuenta que para mayor claridad le he dado a la tabla un nombre parámetros de la pestaña. Constructor (Diseño).

Copiar una ruta desde Explorer o incluso ingresarla manualmente no es, por supuesto, particularmente difícil, pero es mejor minimizar el factor humano y determinar la ruta, si es posible, automáticamente. Esto se puede implementar utilizando la función de hoja de cálculo estándar de Excel CÉLULA (CÉLULA), que puede brindar mucha información útil sobre la celda especificada como argumento, incluida la ruta al archivo actual:

Si asumimos que el archivo de datos de origen siempre se encuentra en la misma carpeta que nuestro Procesador, entonces la ruta que necesitamos se puede formar mediante la siguiente fórmula:

=IZQUIERDA(CELDA(“nombre de archivo”);BUSCAR(“[“;CELDA(“nombre de archivo”))-1)&”Los 100 mejores productos.xls”

o en versión inglesa:

=IZQUIERDA(CELDA(«nombre de archivo»);BUSCAR(«[«;CELDA(«nombre de archivo»))-1)&»Топ-100 товаров.xls»

… donde esta la funcion LEVSIMV (IZQUIERDA) toma un fragmento de texto del enlace completo hasta el corchete de apertura (es decir, la ruta a la carpeta actual), y luego se pega el nombre y la extensión de nuestro archivo de datos de origen.

Parametrizar la ruta en la consulta

Queda el último y más importante toque: escribir la ruta al archivo fuente en la solicitud Los 100 mejores productos.xls, refiriéndose a la celda A2 de nuestra tabla "inteligente" creada parámetros.

Para ello, volvamos a la consulta de Power Query y abrámosla de nuevo Editor avanzado de la pestaña. Revisar (Ver — Editor avanzado). En lugar de una ruta de cadena de texto entre comillas “E:Informes de ventasTop 100 productos.xlsx” Introduzcamos la siguiente estructura:

Excel.Libro de trabajo actual(){[Nombre=”Configuración”]}[Contenido]0 {}[Ruta a los datos de origen]

Veamos en qué consiste:

• Excel.Libro de trabajo actual() es una función del lenguaje M para acceder al contenido del archivo actual
• {[Nombre=”Configuración”]}[Contenido] – este es un parámetro de refinamiento de la función anterior, que indica que queremos obtener el contenido de la tabla "inteligente" parámetros
• [Ruta a los datos de origen] es el nombre de la columna en la tabla parámetrosa la que nos referimos
  
• 0 {} es el número de fila en la tabla parámetrosde la que queremos tomar datos. El límite no cuenta y la numeración parte de cero, no de uno.

Eso es todo, de hecho.

Queda por hacer clic en Acabado y comprobar cómo funciona nuestra solicitud. Ahora, al enviar la carpeta completa con ambos archivos dentro a otra PC, la solicitud permanecerá operativa y determinará la ruta a los datos automáticamente.

• ¿Qué es Power Query y por qué es necesario cuando se trabaja en Microsoft Excel?
• Cómo importar un fragmento de texto flotante en Power Query
• Rediseño de una tabulación cruzada de XNUMXD a una tabla plana con Power Query