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
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 ).
Se abre la ventana Importar datos . Vaya al Ejemplo 6: Actualización de datos 1.csv , selecciónelo y haga clic en Importar .
Se abre un nuevo cuadro de diálogo y muestra una muestra de los datos. 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.
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 .
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.
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).
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).
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
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 .
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.
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 .
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.
¿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.
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 conexiones
Cada 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...
Se abre la ventana Propiedades de consulta .
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
- Introducción a Power Query
- Obtener datos en Power Query: cinco fuentes de datos comunes
- DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
- Utilice el editor de Power Query para actualizar consultas
- Conozca las opciones de Power Query Cerrar Carga
- Parámetros de Power Query: 3 métodos
- Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
- Anexar Power Query: combine rápidamente muchas consultas en 1
- Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
- Listar archivos en una carpeta subcarpetas con Power Query
- Cómo obtener datos del libro actual con Power Query
- Cómo desvincular en Excel usando Power Query (3 formas)
- Power Query: valor de búsqueda en otra tabla con combinación
- Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
- Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
- Declaración If de Power Query: condiciones anidadas ifs múltiples
- Cómo utilizar Power Query Group By para resumir datos
- Cómo utilizar las funciones personalizadas de Power Query
- Power Query: errores comunes, cómo solucionarlos
- Power Query: consejos y trucos
Deja una respuesta