Cómo obtener datos del libro actual con Power Query
En publicaciones anteriores de esta serie , vimos cómo importar datos externos desde un solo archivo , importar todos los archivos en una carpeta e importar datos desde una tabla o rango dentro del mismo libro. Pero, ¿qué pasa si queremos importar TODOS? ¿Los datos dentro del mismo libro? Lo veremos en esta publicación: Use Power Query para obtener datos del libro actual .
Las computadoras y los humanos piensan sobre los datos de manera diferente. A la gente le gusta precategorizar los datos en diferentes áreas. Por ejemplo, podríamos mantener los datos de enero, febrero y marzo en hojas separadas. Sin embargo, las computadoras son más eficientes cuando los datos con la misma estructura se guardan en una tabla, con una columna separada para identificar cada mes. Power Query es sorprendente a la hora de obtener datos del libro de trabajo actual, que está en un formato comprensible para los humanos, y transformarlos a un formato optimizado para computadora.
Tabla de contenido
- Obtenga tablas o rangos individuales del libro de trabajo actual
- Obtener datos del libro de trabajo actual
- Filtrar la tabla de consultas
- ¿Dónde se obtienen los datos de las hojas?
- 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: 0109 Obtener datos del libro de trabajo actual.xlsx
Tener acceso
Comencemos analizando cómo obtener datos de una tabla o rango individual en una consulta. El proceso depende de la ubicación de los datos:
- Tabla : seleccione cualquier celda dentro de la tabla y haga clic en Datos de tabla/rango
- Rango con nombre : seleccione todo el rango con nombre (si es necesario, selecciónelo en el cuadro Nombre), luego haga clic en Datos de tabla/rango
- Matriz dinámica : seleccione la celda superior izquierda de la matriz para mostrar el rango de derrame y luego haga clic en Datos de tabla/rango . En segundo plano, Excel crea un rango con nombre y carga los datos en Power Query
- Rango estándar : seleccione una celda dentro de un rango contiguo y haga clic en Datos de tabla/rango . Excel convertirá el rango en una tabla antes de cargarlo en Power Query
Los rangos estándar se convierten en tablas y las matrices dinámicas tienen rangos con nombre creados automáticamente. Por lo tanto, Power Query solo tiene que trabajar con dos objetos de datos, tablas y rangos con nombre.
A continuación, realizaríamos las transformaciones necesarias. Luego, cuando hayamos terminado, haga clic en Cerrar y Cargar para enviar los datos nuevamente a Excel.
Sin embargo, antes de continuar, veamos los pasos que crea Power Query al usar estos métodos. Por ejemplo, si cargamos una tabla en Excel, el paso Fuente podría verse como el siguiente.
= Excel.CurrentWorkbook(){[Name="tblJanuary"]}[Content]Que está sucediendo aquí:
- Excel.CurrentWorkbook() : la función Power Query que obtiene datos del libro actual
- {[Name=”tblJanuary”]} : el resultado de la función Excel.CurrentWorkbook se filtra para incluir solo elementos con tblJanuary en la columna de nombre.
- [Contenido] – Después del elemento anterior solo hay una fila; este elemento de código profundiza en la columna Contenido de esa fila.
Ahora que entendemos estos elementos, es de esperar que esté de acuerdo en que Excel.CurrentWorkbook es la función que necesitamos para métodos más avanzados.
La guía de Microsoft sobre la función Excel.CurrentWorkbook no contiene mucha información con la que podamos trabajar.
Obtener datos del libro de trabajo actual
Abra el archivo 0109 Obtener datos del archivo workbook.xlsx actual.
Según nuestro conocimiento de la sección anterior, podríamos cargar una tabla o un rango con nombre en Power Query y luego editar el paso Fuente para eliminar las partes del código M que no necesitamos.
= Excel.CurrentWorkbook(){[Name="tblJanuary"]}[Content]Ese es un método simple y agradable. En lugar de eso, ampliaremos un poco nuestras habilidades y comenzaremos con una consulta en blanco.
En Excel, haga clic en Datos Obtener datos de otras fuentes Consulta en blanco
Se abre el Editor de Power Query.
Hay un paso en la ventana Pasos aplicados, nada en la ventana Vista previa y la mayoría de las transformaciones aparecen atenuadas. Si bien la ventana Pasos aplicados muestra Fuente como el paso, no hay nada en este paso en este momento.
Esta es realmente una consulta en blanco.
Si la barra de fórmulas no está visible, haga clic en Ver barra de fórmulas . Luego, en la barra de fórmulas ingresa lo siguiente:
=Excel.CurrentWorkbook()O, si se siente valiente y desea utilizar el Editor avanzado ( Home Advanced Editor ), ingrese lo siguiente:
let Source = Excel.CurrentWorkbook()in SourceRecuerde, el código M distingue entre mayúsculas y minúsculas, por lo que deberá escribir el texto exactamente como se muestra arriba.
La ventana de vista previa muestra los objetos de datos en el libro de trabajo:
- 3 tablas (tblJanuary, tblFebruary y tblMarch)
- Un rango con nombre (myNamedRange)
- Un rango dinámico con nombre (¡enero!Print_Area)
Filtremos la columna de nombre para excluir los elementos que no queremos.
Haga clic en Filtro (icono) Los filtros de texto no son iguales ... En el cuadro de diálogo Filtrar filas , elimine myNamedRange y Print_Area .
A continuación, haga clic en el ícono Expandir para profundizar en la estructura del libro. Desmarque usar el nombre de columna original como prefijo y luego haga clic en Aceptar .
La ventana de vista previa muestra los datos combinados de todos los objetos de datos seleccionados.
Complete la consulta con las siguientes transformaciones:
- Eliminar la columna Nombre
- Aplicar un tipo de datos para cada columna
- Asigne a la consulta un nombre apropiado (he elegido CombinedTable ).
Haga clic en Cerrar carga para insertar los datos en una nueva hoja de trabajo. Acaba de combinar todas las tablas en una sola vista. Increíble, ¿verdad?
Advertencia: aún no lo sabes, pero tienes un problema.
Filtrar la tabla de consultas
El panel Conexiones de consultas muestra 151 filas cargadas.
No realice ningún cambio, pero haga clic en Actualizar datos todos nuevamente.
Err... lo que acaba de pasar. Ahora tenemos 301 filas, pero no hemos agregado más datos.
Si actualizamos los datos nuevamente, tendremos 451 filas. ¡¡¡¡Qué está pasando!!!!
Volvamos a Power Query y veamos qué va mal.
Seleccione el paso Fuente en el cuadro Pasos aplicados. Inicialmente, puede que no parezca haber ningún problema. Pero una vez que hace clic en Inicio Actualizar vista previa , el problema se aclara. La ventana de vista previa ahora muestra esto:
La consulta CombinedTable que creamos carga los datos en Excel como una tabla. Por lo tanto, esta tabla ahora se incluye como un objeto de datos en la lista. Cada vez que se actualiza la consulta, se combina con las otras tablas antes de volver a cargarla en Excel. Como resultado, cada vez que actualizamos, la tabla se hace cada vez más larga.
Solucionemos este problema.
Necesitamos cambiar nuestro paso de filtro. ¿Pero qué tipo de filtro queremos? Por ejemplo, ¿queremos eliminar CombinedTable o queremos incluir solo las tablas tblJanuary , tblFebruary y tblMarch ? Esta pregunta determina qué tipo de filtro debemos aplicar.
En este ejemplo, he decidido incluir todos los elementos con el prefijo tbl . Edite el paso de filtro anterior para incluir solo elementos que comiencen con tbl en el cuadro de diálogo Filas filtradas.
El código M en la barra de fórmulas será:
= Table.SelectRows(Source, each Text.StartsWith([Name], "tbl"))Esto elimina todos los objetos que no queremos, incluida la tabla CombinedTable . Pero permite que otros objetos con un prefijo tbl se incluyan automáticamente en el alcance de la consulta.
Notas :
- Tener convenciones de nomenclatura estándar es fundamental para tener éxito con este método.
- La carga de datos cada vez mayor es sólo un problema con las tablas de Excel; Si los datos se cargan en otra fuente (por ejemplo, solo conexión), no aparecerán como un objeto de datos usando Excel.CurrentWorkbook.
¿Dónde se obtienen los datos de las hojas?
Como hemos visto, podemos obtener datos de Tablas y rangos con nombre, pero ¿qué pasa con las hojas? Eso sería útil.
Desafortunadamente, al momento de escribir este artículo, no existe ninguna opción para obtener datos de las hojas de trabajo en el libro de trabajo actual. En mi opinión, esto es una omisión. Es una característica que se ha solicitado a Microsoft muchas veces. Quizás algún día nos lo den.
Conclusión
En esta publicación, hemos visto lo fácil que es obtener datos del libro actual mediante Power Query. Todo lo que hizo falta fue un poco de comprensión de la función Excel.CurrentWorkbook.
Al aplicar este método, si carga los datos en una tabla de Excel, no olvide filtrar la tabla combinada para detener la carga de datos cada vez mayor.
Leer más publicaciones en esta serie
- 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