Actualizar Power Query en Excel: 4 formas + opciones avanzadas

Anteriormente, vimos cómo importar datos de varios formatos de archivo y cargarlos en Excel usando Power Query. En esta publicación, pasamos a considerar cómo actualizar esos datos. Actualizar los datos es importante ya que nos permite crear una consulta una vez y usarla una y otra vez (es decir, la automatización en acción).

Tabla de contenido
  • Actualizar consulta de energía
    • Crear una consulta básica
    • Transformaciones simples
  • Actualizar la consulta
  • Usando VBA para actualizar todo
  • Actualizar consultas específicas
    • botón actualizar
    • Menús de conexiones de consultas
    • actualización de VBA
  • Opciones de actualización avanzadas
  • Actualización de Power Query en Excel Online
  • Envolver

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

Nombre del archivo: 0090 Actualizar Power Query.zip

Tener acceso

La descarga incluye 2 archivos:

  • Ejemplo 6: Actualización de datos 1.csv
  • Ejemplo 6: Actualización de datos 2.csv
Índice
  1. Actualizar consulta de energía
    1. Crear una consulta básica
    2. Transformaciones simples
  2. Actualizar la consulta
  3. Usando VBA para actualizar todo
  4. Actualizar consultas específicas
    1. botón actualizar
    2. Al cargar los datos de Power Query en Excel, se abrió el menú Consultas y Conexiones . Esta ventana muestra todas las consultas creadas en el libro de trabajo. Si ese menú no está abierto, haga clic en Consultas de datos y conexionesCada consulta enumerada en Consultas y Conexión tiene un ícono de actualización . Simplemente haga clic en el icono para actualizar los datos de esa consulta. Alternativamente, podemos hacer clic derecho en la consulta y seleccionar Actualizar en el menú.actualización de VBA
  5. Opciones de actualización avanzadas
  6. Actualización de Power Query en Excel Online
  7. Envolver

Actualizar consulta de energía

A diferencia del motor de cálculo de Excel, que de forma predeterminada vuelve a calcular con cada cambio en la hoja de cálculo, Power Query solo vuelve a calcular cuando se le indica específicamente.

El proceso de actualización básico es sencillo: haga clic en Actualizar datos todos . Sin embargo, hay muchas opciones para personalizar la actualización y optimizarla para su escenario. También analizamos las opciones de actualización más adelante en esta publicación.

Los siguientes ejemplos utilizan el Ejemplo 6: Actualización de datos 1.csv y el Ejemplo 6: Actualización de datos 2.csv de las descargas.

Crear una consulta básica

Para demostrar las capacidades de actualización de datos de Power Query, primero debemos crear una consulta.

Para trabajar junto con el ejemplo, abra un nuevo libro y haga clic en Datos de texto/CSV (también disponible en Datos Obtener datos de archivo de texto/CSV ).

Datos: desde texto o CSV

Se abre la ventana Importar datos . Vaya al Ejemplo 6: Actualización de datos 1.csv , selecciónelo y haga clic en Importar .

Seleccione el archivo fuente para Power Query

Se abre un nuevo cuadro de diálogo y muestra una muestra de los datos. Haga clic en Transformar datos .

Ventana de muestra, haga clic en Transformar datos

Esto carga los datos de muestra en la ventana de vista previa.

Transformaciones simples

Ahora haremos algunas transformaciones de datos sencillas para convertir el CSV en información útil. Esta es la primera vez en esta serie que realizamos algunas de estas transformaciones, por lo que es una buena idea seguirlas y comprender la interfaz de usuario. Definitivamente usaremos estas transformaciones nuevamente en el futuro.

Eliminar el paso Tipo modificado

El primer paso a seguir es eliminar el paso Tipo modificado haciendo clic en la cruz al lado del paso.

Cambiar tipo de datos con configuración regional

Hay problemas específicos con las fechas en Power Query. El archivo CSV tiene el formato de fecha del Reino Unido de dd/mm/aaaa, pero si su región no acepta ese formato de fecha, puede generar errores. Por lo tanto, eliminamos el paso Tipo modificado aplicado automáticamente, lo que puede causar este problema.

Aplicar fecha según la configuración regional

Para obtener un formato de fecha válido para su región:

  • Haga clic en el ícono ABC en el encabezado de la columna Fecha
  • En el menú, seleccione Usando configuración regional… .
  • En el cuadro de diálogo Cambiar tipo con local, aplique lo siguiente:
    • Tipo de datos: Fecha
    • Localidad: Inglés (Reino Unido)

Haga clic en Aceptar .

Cambiar tipo de datos con configuración regional

Esto le indica a Power Query que la columna contiene un formato de fecha del Reino Unido.

Cambiar tipos de datos

Ahora, volvamos a aplicar el tipo de datos predeterminado para las columnas restantes. Haga clic en Cliente, mantenga presionada la tecla Mayús y haga clic en Valor. Luego, haga clic en Transformar tipo de datos de detección en la cinta.

Transformar - Detectar tipo de datos

Power Query ahora debería tener los tipos de datos correctos para cada columna.

Cambiar fecha a fecha de fin de mes

Estamos creando un informe basado en fechas de fin de mes, así que cambiemos la fecha. Haga clic en la columna Fecha . En la cinta, haga clic en Transformar fecha mes Fin de mes (esto cambiará la columna Fecha al último día del mes calendario).

Cambiar fecha a una fecha de fin de mes

Eliminar columnas

Hay dos columnas que no necesitamos para nuestro ejemplo. Seleccione las columnas Producto y Vendido por . En la cinta, haga clic en Inicio Eliminar columnas (esto elimina las columnas seleccionadas).

Inicio - Eliminar columnas

Pivotar los datos

Ahora centrémonos en los datos. Seleccione la columna Fecha y haga clic en Transformar columna dinámica .

Se abre el cuadro de diálogo Columna dinámica . Aplique la siguiente configuración:

  • Cambie la columna Valores a Valor (esta es la columna que se agregará)
  • En la sección de opciones avanzadas , configure la Función de valor agregado en Suma
  • Haga clic en Aceptar

Columna dinámica en Power Query

Finalmente, haga clic en Inicio Cerrar carga (menú desplegable) Cerrar carga en…

Luego, en el cuadro de diálogo Importar datos , seleccione una tabla en una nueva hoja de trabajo y haga clic en Aceptar .

Cerrar y cargar datos en Excel

Excel crea una nueva hoja de trabajo con un informe que contiene las ventas de los clientes de enero y febrero. Esta tabla contiene los datos del CSV, que ha sido transformado. Debería verse como la captura de pantalla a continuación.

Datos cargados en Excel

Actualizar la consulta

Para demostrar el proceso de actualización, estamos simulando dónde un usuario podría recibir un archivo nuevo diariamente, semanalmente o mensualmente.

  • Elimine el archivo Ejemplo 6: Actualización de datos 1.csv
  • Cambie el nombre del archivo Ejemplo 6: Actualización de datos 2.csv a Ejemplo 6: Actualización de datos 1.csv

Cuando actualizamos, Power Query busca un archivo llamado Ejemplo 6: Actualización de datos 1.csv. No sabe que es un archivo diferente. De hecho, no queremos que Power Query sepa que ha sido reemplazado, solo queremos que procese lo que hay allí.

Haga clic en Actualizar datos todos .

Datos - Actualizar todo

En segundo plano, Excel importa los datos del archivo CSV, se aplican las mismas transformaciones y los datos se cargan en la hoja de trabajo como una tabla.

Los nuevos datos deberían aparecer automáticamente en la hoja de trabajo.

Nuevos datos agregados automáticamente al actualizar

¿Notaste que los datos de marzo aparecieron con un solo clic? ¡ Es increíble !

Cada vez que hay un cambio en los datos existentes, o cuando se recibe un nuevo archivo, solo es necesario guardar el archivo con la misma ruta y luego hacer clic en un botón. Eso es algo poderoso… ¿verdad?

En una publicación futura, veremos cómo vincular la ruta del archivo a una celda , para que pueda importar sin tener que cambiar el nombre de ningún archivo.

Usando VBA para actualizar todo

El código de VBA para actualizar todas las consultas es el mismo que para actualizar todas las tablas dinámicas. Ingrese el fragmento de código a continuación en un módulo de código VBA estándar.

Sub RefreshAll()ActiveWorkbook.RefreshAllEnd Sub

Si adjuntamos esta macro a un botón de control de forma o formulario, creamos una interfaz de usuario simple para cualquiera que use nuestro libro de trabajo para actualizar consultas.

Actualizar consultas específicas

Cuando tenemos muchas consultas dentro de un libro, puede llevar un tiempo actualizarlas todas. En estas circunstancias, puede ser útil actualizar solo las consultas que necesitamos. Hay algunas formas de lograrlo.

botón actualizar

Si hace clic en una tabla que proviene de una consulta, podemos hacer clic en Actualizar datos todos (menú desplegable) Actualizar para actualizar solo esa consulta específica.

Actualizar una consulta individual

Al cargar los datos de Power Query en Excel, se abrió el menú Consultas y Conexiones . Esta ventana muestra todas las consultas creadas en el libro de trabajo. Si ese menú no está abierto, haga clic en Consultas de datos y conexionesCada consulta enumerada en Consultas y Conexión tiene un ícono de actualización . Simplemente haga clic en el icono para actualizar los datos de esa consulta. Alternativamente, podemos hacer clic derecho en la consulta y seleccionar Actualizar en el menú.actualización de VBA

Con una macro de VBA, podemos actualizar consultas individuales. El siguiente fragmento de código actualiza la consulta Ejemplo 6: Actualización de datos 1 .

Sub RefreshQuery()ActiveWorkbook.Connections("Consulta - Ejemplo 6 - Actualización de datos 1").RefreshEnd Sub

Esta es una técnica útil cuando:

  • queremos proporcionar una interfaz fácil de usar para el usuario
  • queremos controlar el orden de actualización de la consulta

Opciones de actualización avanzadas

A medida que se agregan más consultas a un libro, actualizar las consultas individuales puede llevar mucho tiempo o demasiado lento actualizarlas todas. La buena noticia es que Excel ya cuenta con opciones avanzadas para controlar la actualización.

Seleccione una celda dentro de una tabla de consulta y luego haga clic en Actualizar datos todas las propiedades de conexión (desplegables). O haga clic derecho en la consulta en el panel Consultas y conexiones y haga clic en Propiedades...

Consultar propiedades para opciones de actualización

Se abre la ventana Propiedades de consulta .

Opciones de actualización de consultas

Las opciones de actualización importantes disponibles en esta ventana son:

Actualización en segundo plano: esto nos permite seguir trabajando mientras los datos se actualizan en segundo plano. Está activado de forma predeterminada. Si eliminamos esta opción, no podremos usar Excel hasta que se complete el proceso de actualización.

Sin embargo, esta opción tiene consecuencias mayores. Si tenemos habilitada la actualización en segundo plano:

  • Las tablas dinámicas se actualizan antes de Power Query. Esto es un problema si nuestra tabla dinámica se basa en una consulta; significa que debemos hacer clic en actualizar dos veces.
  • Las macros pueden ejecutarse antes de que se complete la actualización de la consulta.

Si la actualización en segundo plano está deshabilitada, la actualización se produce en el orden correcto; Las tablas dinámicas se actualizan y las macros de VBA se ejecutan después de que se haya actualizado la consulta. Si bien puede bloquear Excel por un corto período de tiempo, es mejor saber que los datos son correctos. Generalmente, recomiendo deshabilitar la actualización en segundo plano para todas las consultas.

Actualizar cada x minutos: esta opción controla la frecuencia con la que se actualiza una consulta. Esto sólo funciona si el libro está abierto. Es útil cuando los datos de origen cambian periódicamente. Probablemente sea una buena idea utilizar esta opción sólo con la actualización en segundo plano habilitada, o podría resultar muy molesto para los usuarios esperar a que Excel se actualice periódicamente.

Actualizar al abrir el archivo: actualizar los datos automáticamente al abrir el archivo es una característica útil, ya que sabe que los datos están actualizados cada vez que abre el archivo.

Actualice esta conexión en Actualizar todo: cuando una consulta contiene datos estáticos, no es necesario actualizarla cada vez. Al deshabilitar la opción Actualizar esta conexión al Actualizar todo , se eliminan estas consultas del proceso Actualizar todo, lo que a su vez reduce el tiempo de actualización.

Habilite la carga rápida de datos: todos queremos que los datos se carguen más rápido, ¿verdad? Entonces, ¿por qué no haría clic en la opción Habilitar carga rápida de datos? Bueno... puede hacer que Excel se bloquee durante períodos de tiempo al cargar, pero hará que los datos se carguen más rápido. Un estudio realizado por Andrew Moss indicó que el ahorro de tiempo para la carga de Fast Data fue del 18% al 33%. Personalmente, siempre quiero los datos más actualizados, así que recomiendo verificarlos.

Actualización de Power Query en Excel Online

Al momento de escribir este artículo, Microsoft ha comenzado a avanzar en la incorporación de Power Query a Excel Online. Actualmente solo podemos actualizar una pequeña cantidad de fuentes de datos:

  • Desde mesa/gama
  • De fuentes anónimas de OData

Todavía queda un largo camino por recorrer antes de que Power Query esté completamente disponible en Excel en línea.

Encuentre más información aquí: https://techcommunity.microsoft.com/t5/excel-blog/new-in-excel-for-the-web-power-query-refresh-is-now-generally/ba-p/3300369

Envolver

Esta publicación nos muestra cómo actualizar Power Query. Al actualizar, los datos se vuelven a importar y siguen los mismos pasos de transformación. A través de esto, Power query nos permite automatizar todos estos pasos con un solo clic.


Lea más publicaciones en la serie Introducción a Power Query

  1. Introducción a Power Query
  2. Obtener datos en Power Query: cinco fuentes de datos comunes
  3. DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
  4. Utilice el editor de Power Query para actualizar consultas
  5. Conozca las opciones de Power Query Cerrar Carga
  6. Parámetros de Power Query: 3 métodos
  7. Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
  8. Anexar Power Query: combine rápidamente muchas consultas en 1
  9. Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
  10. Listar archivos en una carpeta subcarpetas con Power Query
  11. Cómo obtener datos del libro actual con Power Query
  12. Cómo desvincular en Excel usando Power Query (3 formas)
  13. Power Query: valor de búsqueda en otra tabla con combinación
  14. Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
  15. Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
  16. Declaración If de Power Query: condiciones anidadas ifs múltiples
  17. Cómo utilizar Power Query Group By para resumir datos
  18. Cómo utilizar las funciones personalizadas de Power Query
  19. Power Query: errores comunes, cómo solucionarlos
  20. Power Query: consejos y trucos

Este sitio utiliza cookies para una mejor experiencia - Mas información