Actualización automática de Power Query al cambiar el parámetro

Hace unos días, un lector hizo una pregunta sobre el uso de parámetros en Power Query; querían actualizar automáticamente Power Query cada vez que cambia un parámetro.

"Una pregunta, ¿cómo se configura la consulta para que se actualice automáticamente cuando cambia un valor?"

No he probado esto antes, pero puedo entender los beneficios. Reduce la necesidad de hacer clic en Actualizar y crea un comportamiento más cercano a las fórmulas estándar de Excel. Entonces parecía una pregunta que merecía su propia publicación.

Este es el resultado que intentamos conseguir:

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: 0013 Actualización automática Power Query.zip

Tener acceso

Consulta de energía de actualización automática con una macro

Hay dos formas en que Excel activa eventos cuando cambian los valores de las celdas:

1. Función definida por el usuario (UDF)
2. Evento de cambio de hoja de trabajo

Me encanta usar UDF para controlar objetos en Excel, como cambiar los colores de las pestañas o establecer el mínimo y el máximo de un eje de gráfico . Sin embargo, probé una UDF para este escenario de actualización automática, pero simplemente no era estable y provocó que Excel fallara. Si logras lograrlo, házmelo saber en los comentarios para que todos podamos aprender de tu dominio de VBA.

Eso deja la opción 2, el evento de cambio de hoja de trabajo.

1. Vaya al Editor de Visual Basic haciendo clic en Desarrollador – Visual Basic (si no tiene visible la cinta Desarrollador, puede usar el atajo de teclado Alt + F11 ).
   
2. Abra el módulo de código de la hoja de trabajo que contiene el parámetro. En el archivo de ejemplo, la pestaña Resultado de la consulta contiene la celda con el parámetro.
   
3. Copie el siguiente código en el módulo de hoja.

   Sub Worksheet_Change(ByVal Target As Range)Si Target.Address = Me.Range(" G6 ").Address _ O Target.Address = Me.Range(" G3 ").Address _ Entonces ActiveWorkbook.Connections(" Consulta - SalesData " ).RefreshEnd IfEnd Sub

4. A continuación, adapte el código a su escenario cambiando lo siguiente:
   Las referencias de celda (resaltadas en violeta en el código anterior) son las celdas que contienen sus parámetros. En el archivo de ejemplo, hay dos parámetros en las celdas G6 y G3.
   El nombre de la consulta (resaltado en verde en el código anterior) será la cadena "Consulta -" + nombre de la consulta a actualizar. En el archivo de ejemplo, la consulta se llama SalesData. Por lo tanto, la cadena completa es Consulta – SalesData.
   Si necesita más o menos parámetros, agregue o elimine instancias de la siguiente línea de código:

   O Target.Address = Me.Range(" G3 ").Dirección _

5. Cierra el editor de Visual Basic

Eso es todo; Ahora la macro está lista para usar. Cambie el parámetro y observe cómo la macro actualiza automáticamente la consulta.

NOTAS:

1. Los libros que contienen macros no se pueden guardar como archivos .xlsx. En su lugar, debemos utilizar el formato .xlsm.
2. Como comportamiento estándar de Excel, cuando se ejecuta una macro, se borra la pila Deshacer. Por lo tanto, no podrá utilizar la función Deshacer después de que se ejecute la macro.

Otra opción: actualizar cada n minutos

Otra opción, que no satisface del todo los requisitos del lector, pero que podría satisfacer los suyos, es actualizar automáticamente la consulta a intervalos establecidos. Haciendo esto:

1. • Evita las macros y por tanto podemos utilizar el tipo de archivo estándar .xlsx
   • Todavía evita la necesidad de hacer clic en actualizar
   • No proporcionará una actualización instantánea, por lo que no emula el comportamiento de la fórmula de recalcular automáticamente

Para implementar esto:

1. 1. En la cinta, seleccione Datos – Conexiones de consultas
      
   2. Haga clic derecho en la consulta y seleccione Propiedades… en el menú.
      
   3. Marque la opción Actualizar cada n minutos e ingrese un intervalo de tiempo.
      
   4. Haga clic en Aceptar para cerrar el cuadro de diálogo Propiedades de consulta.

La consulta ahora se actualizará automáticamente en el intervalo establecido. Si cumple con sus requisitos, es una mejor opción que una macro.

¿Qué sigue?

Si su consulta se carga directamente en una tabla dinámica, esta publicación de Jon Acampora será un buen siguiente paso.

O, si desea obtener más información sobre el uso del editor Power Query, consulte esta publicación .

---