Como Cambiar el origen de Power Query según un valor de celda

Si es como yo, primero crea soluciones de Power Query en un entorno de prueba; luego, cuando está listo, se libera en el medio silvestre. Esto significa que se deben actualizar las rutas de todos los archivos fuente. O tal vez seas un consultor que crea soluciones para otros; Cuando distribuye el libro de trabajo a su cliente, debe proporcionarle instrucciones complicadas sobre cómo actualizar las fuentes de datos para su entorno. ¿No sería mejor si la fuente pudiera vincularse a un valor de celda que nosotros, o un cliente, pudiéramos actualizar fácilmente? ¡Sí lo haría! Entonces, en esta publicación, veremos cómo hacerlo; cómo cambiar la fuente de Power Query en función de un valor de celda.

He escrito antes sobre cómo cambiar las fuentes de Power Query ( Power Query: cambiar la ubicación de los datos de origen ). En esa publicación, muestro el método manual y un método que usa parámetros. Lo que estoy a punto de mostrarles es un método aún más sencillo.

Debo agradecerle a Celia Alves por compartir esta técnica en una reciente reunión en línea.

Tabla de contenido

• El escenario
• Crear rangos con nombre
• Usar rango con nombre dentro del código M
• Rangos con nombre de referencia en código M
• Utilice rangos con nombre como fuente
• Haga que la ruta del archivo sea dinámica (no para OneDrive)
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0021 Power Query: cambiar la fuente según el valor de la celda.zip

Tener acceso

El archivo de descarga incluye tres archivos:

1. Archivo de origen: los datos cargados en Power Query
2. Archivo de inicio para el ejemplo.
3. Archivo completo para el ejemplo.

El escenario

No voy a proporcionar detalles sobre cómo cargar los datos de origen en Power Query; Supongo que ya sabes cómo hacerlo.

Si está utilizando los archivos de ejemplo, los datos cargados desde el archivo fuente se verá así:

La celda C2 contiene el nombre del archivo y la celda C3 contiene la ruta de la carpeta. Actualmente no están vinculados a nada; Actualmente es solo texto en una celda. Pero al final de esta publicación, estas celdas estarán conectadas a Power Query.

Estas son ubicaciones de archivos en mi PC, por lo que no funcionarán para usted. Cambie el nombre del archivo y la ruta de la carpeta a la ubicación de su archivo fuente.

NOTA: Un error común es olvidar la barra invertida al final de la ruta de la carpeta.

Crear rangos con nombre

Ahora, crea dos rangos con nombre:

Seleccione la celda C2 (el nombre del archivo) y cree un rango con nombre llamado FileName . Para hacer esto, escriba el nombre del rango nombrado en el cuadro de nombre y presione Entrar.

A continuación, repetimos el paso anterior para la ruta del archivo. Seleccione la celda C3 (la ruta del archivo) y cree un rango con nombre llamado FilePath .

Combinaremos estos dos rangos con nombre en el código M de Power Query. Por lo tanto, asegúrese de que formen una ruta de archivo válida.

Usar rango con nombre dentro del código M

Ahora sólo necesitamos usar los rangos nombrados en nuestra consulta.

Abra el panel Consultas y conexiones haciendo clic en Datos – Consultas y conexiones .

Haga doble clic en el nombre de la consulta para abrir el Editor de consultas.

]

En el editor de Power Query, haga clic en Ver – Editor avanzado .

Rangos con nombre de referencia en código M

Agregaremos nuevos pasos directamente después de la declaración let.

FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Contenido]{0}[Columna1],FileName = Excel.CurrentWorkbook(){[Name="FileName"]}[Contenido]{0}[Columna1 ],

Estas líneas de código M son efectivamente las mismas, pero una es para la ruta del archivo y otra para el nombre del archivo. El desglose del código para la primera línea es el siguiente:

• FilePath = : El nombre del paso en Power Query
• Excel.CurrentWorkbook() : función Power Query que utiliza datos del libro actual
• {[Name=”FilePath”]} : el nombre del rango nombrado
• [Contenido]{0}[Columna1] : utiliza la primera fila y la primera columna de la tabla del rango nombrado

RECUERDE: Asegúrese de que cada línea de código en el Editor avanzado esté terminada con una coma.

En este ejemplo, tenemos dos rangos con nombre, pero podemos crear tantos como necesitemos.

Utilice rangos con nombre como fuente

Finalmente, necesitamos insertar los pasos FilePath y FileName en el paso Fuente.

Reemplace esto:

Fuente = Excel.Workbook(File.Contents( "C:UsersmarksOneDriveDocumentsSourceFile.xlsx" ), nulo, verdadero),

con este:

Fuente = Excel.Workbook(File.Contents( FilePath FileName ), nulo, verdadero),

Haga clic en Listo para cerrar el Editor de consultas. Luego cierre y cargue los datos nuevamente en Excel.

Suponiendo que hemos hecho todo correctamente, los datos de la consulta ahora están vinculados a las celdas. Podemos cambiar los valores en las celdas C2 y C3, luego hacer clic en actualizar y ¡Ta-dah! Los datos se actualizarán al nuevo archivo fuente. Logramos cambiar la fuente de Power Query según un valor de celda.

Haga que la ruta del archivo sea dinámica (no para OneDrive)

Si los archivos de origen están contenidos en la misma carpeta que el libro que contiene la consulta, es posible utilizar una ruta de archivo dinámica. Intente ingresar lo siguiente en la celda C3.

=IZQUIERDA(CELDA("nombre de archivo",$A$1),BUSCAR("[",CELDA("nombre de archivo",$A$1),1)-1)

La fórmula se actualizará automáticamente para mostrar la ruta correcta del archivo.

Notas:

• Se debe guardar el libro que contiene la consulta.
• Los libros guardados en OneDrive mostrarán la URL https, en lugar de la ruta del archivo local, lo que dificulta el uso de este método sin un mayor desarrollo.

Conclusión

Eso es todo, hemos aprendido cómo cambiar la fuente de Power Query según el valor de una celda. Todo lo que se necesita son algunos rangos con nombre y unas pocas líneas cortas de código M.

Ahora cualquier usuario puede cambiar fácilmente la celda para redirigir la consulta a la ubicación correcta de los datos de origen. Ni siquiera necesitan abrir Power Query ni comprender cómo funciona.

---