Agregar varias hojas en Excel a LuckyTemplates

Agregar varias hojas en Excel a LuckyTemplates

Este tutorial tratará sobre cómo importar y abrir un archivo de Excel con varias hojas en una tabla de LuckyTemplates.

Este archivo de datos de Excel se utilizará para este tutorial.

Agregar varias hojas en Excel a LuckyTemplates

El archivo contiene 60 hojas de datos con el mismo diseño.

Agregar varias hojas en Excel a LuckyTemplates

El objetivo de este tutorial es cargar todas las hojas de Excel en LuckyTemplates y recuperar la información de Bond como una tabla. Debe obtener el ticker de Seguridad y agregarlo en una columna separada. Luego, agréguelos todos juntos en una sola tabla que aproveche la interfaz de usuario para generar el código que necesita.

Tabla de contenido

Importación de las hojas de Excel a LuckyTemplates

Lo primero que debe hacer es abrir el Editor de Power Query y crear un parámetro para almacenar la ubicación del archivo mediante la opción Administrar parámetros .

Agregar varias hojas en Excel a LuckyTemplates

Dentro del cuadro Administrar parámetros, haga clic en Nuevo e ingrese FileLocation como el nombre del parámetro. A continuación, establezca el Tipo de datos en Texto y los Valores sugeridos en Cualquier valor . Copie la ruta del archivo del archivo de Excel y péguelo en los campos Valor actual.

Agregar varias hojas en Excel a LuckyTemplates

Después de presionar Aceptar, haga clic en Nueva fuente y seleccione Libro de Excel .

Agregar varias hojas en Excel a LuckyTemplates

A continuación, seleccione y abra el archivo de Excel. Una vez hecho esto, la pantalla Navegador se abrirá y enumerará todas las hojas dentro del archivo de Excel.

Agregar varias hojas en Excel a LuckyTemplates

Seleccione la primera hoja y presione OK. Después de eso, verá la tabla dentro de Power Query Editor.

Agregar varias hojas en Excel a LuckyTemplates

Lo siguiente que debe hacer es cambiar la ruta del archivo codificada en el parámetro del archivo. Abra la ventana del Editor avanzado y cambie la ruta del archivo en el código fuente a FileLocation .

Agregar varias hojas en Excel a LuckyTemplates

Es mejor tener sus datos en tablas de Excel porque los límites o el rango de datos se definen antes de incorporarlos a Power Query . Dado que el archivo contiene hojas y no tablas, corre el riesgo de traer columnas y filas vacías, por lo que debe solucionarlo.

Sacar el teletipo de seguridad de Excel a LuckyTemplates

Saber que el diseño de las hojas es fijo ayuda a crear una solución, especialmente si desea maximizar y utilizar la interfaz de usuario para generar el código. Por ejemplo, un requisito es agregar una columna que contenga el indicador de seguridad. Si miras los datos, puedes ver el ticker.

Agregar varias hojas en Excel a LuckyTemplates

La interfaz de usuario puede ayudarlo a extraer el valor del ticker. Haga clic con el botón derecho en la celda y seleccione Agregar como nueva consulta .

Agregar varias hojas en Excel a LuckyTemplates

En la barra de fórmulas, verá que el nombre de la tabla va seguido de un número de fila de base cero entre llaves. Los corchetes se llaman operadores de índice posicional . También verá el nombre del campo encerrado entre corchetes, que se denominan operadores de acceso al campo .

Agregar varias hojas en Excel a LuckyTemplates

Con esos códigos, ahora puede extraer el valor. Regrese a la tabla de bonos y obtenga los datos de bonos. Primero, elimine las 8 ocho filas superiores. Haga clic en Quitar filas y seleccione Quitar filas superiores .

Agregar varias hojas en Excel a LuckyTemplates

A continuación, ingrese 8 en el campo Número de filas y presione OK.

Agregar varias hojas en Excel a LuckyTemplates

Luego, haga clic en Usar la primera fila como encabezados para configurar los encabezados.

Agregar varias hojas en Excel a LuckyTemplates

Una vez hecho esto, la tabla de bonos ahora se verá así.

Agregar varias hojas en Excel a LuckyTemplates

Directamente debajo del encabezado, verá una línea delgada que representa la calidad de la columna. A partir de ahí, puede ver que hay bastantes espacios en blanco en las columnas. Esto significa que el archivo trajo muchas filas vacías.

Agregar varias hojas en Excel a LuckyTemplates

Eliminar filas vacías

Para eliminar las filas vacías, haga clic en Eliminar filas y seleccione Eliminar filas en blanco .

Agregar varias hojas en Excel a LuckyTemplates

Esa transformación genera esta sintaxis:

Agregar varias hojas en Excel a LuckyTemplates

Record.FieldValues ​​obtiene todos los valores de la fila actual en la tabla como una lista. List.RemoveMatchingItems quita todos los valores de la primera lista que tienen una coincidencia en la segunda lista. La segunda lista solo contiene una cadena de texto vacía o un valor nulo. Estos son los valores que serán excluidos de la primera lista.

Si todas las cadenas de texto vacías y nulas se han eliminado de la lista con los valores de campo de registro, la lista debe estar vacía y List.IsEmpty se evaluará como True. Luego, Table.SelectRows retendrá los Trues.

No debe terminar con una tabla con solo filas en blanco. Es por eso que la palabra clave not se agrega antes de List.IsEmpty . Eso luego devuelve una tabla que contiene filas que no están en blanco.

Además de las filas en blanco, también debe eliminar las columnas en blanco. Pero antes de eso, eche un vistazo a lo que genera Power Query cuando elimina una columna. Seleccione la cuarta columna y haga clic en Quitar columnas .

Agregar varias hojas en Excel a LuckyTemplates

Después de realizar la transformación, verá esta sintaxis en la barra de fórmulas.

Agregar varias hojas en Excel a LuckyTemplates

Llama a la función Table.RemoveColumns, luego hace referencia y pasa el paso anterior en el panel Pasos aplicados como primer argumento. La transformación pasa además una lista que contiene el nombre de columna de las columnas que desea eliminar.

Duplicar una consulta

Ahora, duplique la consulta y seleccione el paso Promocionar encabezados en el panel Pasos aplicados. A continuación, haga clic con el botón derecho en ese paso y seleccione Eliminar hasta el final .

Agregar varias hojas en Excel a LuckyTemplates

Recuerde que puede usar el operador de índice posicional para pasar un número de fila basado en cero en un conjunto de corchetes. Así que mantenga solo la primera fila ingresando 0 dentro de dos corchetes en la barra de fórmulas.

Agregar varias hojas en Excel a LuckyTemplates

Una vez hecho esto, la tabla de bonos se verá así.

Agregar varias hojas en Excel a LuckyTemplates

A partir de ahí, reutilice una parte de la lógica creada por el paso Filas en blanco eliminadas para convertir el registro en una lista y eliminar el valor nulo. Vuelva a la consulta de bonos y seleccione el paso Quitar filas en blanco. A continuación, copie este código M.

Agregar varias hojas en Excel a LuckyTemplates

Vuelva a la consulta duplicada y pegue el código en la barra de fórmulas. Luego, organice algunas cadenas para que la fórmula se vea así.

Agregar varias hojas en Excel a LuckyTemplates

Ahora ha creado una lista con los nombres de las columnas que desea conservar. Cambie el nombre de la consulta a Nombres de columna .

Agregar varias hojas en Excel a LuckyTemplates

Luego, regrese a la consulta de Bond. Dado que ha creado una consulta que contiene todas las columnas que desea conservar, debe enumerar todas las columnas que desea excluir en la función Table.RemoveColumns .

Agregar varias hojas en Excel a LuckyTemplates

Cambie {Column4} con la misma sintaxis que se copió de los pasos de Eliminación de filas en blanco. Cambie también {“”, null} a ColumnNames .

Agregar varias hojas en Excel a LuckyTemplates

Luego, debe pasar una lista con los nombres de columna reales de la tabla Bond. Cambie Record.FieldValues(_) a Table.ColumnNames() . Ingrese # "Filas en blanco eliminadas" dentro de los paréntesis para pasar un argumento de referencia de tabla.

Agregar varias hojas en Excel a LuckyTemplates

Asignación de tipos de datos a columnas

Lo siguiente que debe hacer es asignar los tipos de datos apropiados a las columnas. Para la columna Fecha, haga clic en el icono junto al encabezado y seleccione Fecha.

Agregar varias hojas en Excel a LuckyTemplates

Para las columnas PX_LAST y YLD_YTM_MID, seleccione el tipo de datos Número decimal.

Agregar varias hojas en Excel a LuckyTemplates

Con estas tres consultas, ha creado todos los componentes básicos que puede usar para diseñar una solución que aborde todas las hojas del archivo de Excel . Para ello, debe duplicar la consulta de enlace y eliminar todos los pasos excepto el paso de origen en el panel Pasos aplicados.

Adición de la columna de teletipo de seguridad de Excel a LuckyTemplates

En el paso Fuente, puede ver todos los datos dentro del archivo de Excel. En lugar de crear una consulta separada para cada hoja, puede usar la consulta de enlace y transformar las tablas anidadas en la columna Datos.

Primero, agregue el ticker de Seguridad. Si hace clic en el espacio en blanco dentro de la celda de cualquier tabla, verá una vista previa del contenido de esa tabla.

Agregar varias hojas en Excel a LuckyTemplates

Debe crear una lógica utilizando una instrucción para obtener el indicador de seguridad. Vaya a la consulta Column2 y copie la dirección del ticker de seguridad de la barra de fórmulas.

Agregar varias hojas en Excel a LuckyTemplates

A continuación, vuelva a la consulta de bonos y agregue una columna personalizada.

Agregar varias hojas en Excel a LuckyTemplates

Nombre la columna Security Ticker y escriba el siguiente código M.

Agregar varias hojas en Excel a LuckyTemplates

La fórmula tiene una declaración IF que dice que si la palabra Seguridad se encuentra en la Columna 1, proporcionará el valor de la celda de la Columna 2 que se encuentra junto a ella. De lo contrario, se proporcionará un valor nulo.

Después de presionar Aceptar, se agregará a la tabla una nueva columna con los tickers de Seguridad.

Agregar varias hojas en Excel a LuckyTemplates

Haga clic en el botón desplegable de la columna Security Ticker y anule la selección de nulo para eliminar todos los valores nulos de la columna.

Agregar varias hojas en Excel a LuckyTemplates

Después de eso, te quedará toda la información de los Bonos de todas las hojas. Todo lo que necesita hacer es repetir las transformaciones realizadas en la consulta Bond(2) y aplicarlas a las tablas anidadas en la columna Datos.

Creación de una columna personalizada para consulta de bonos

Vaya a la consulta Bond(2), abra la ventana del Editor avanzado y copie el siguiente código:

Agregar varias hojas en Excel a LuckyTemplates

A continuación, vuelva a la consulta de bonos y cree otra columna personalizada. Dado que necesita aplicar múltiples transformaciones en múltiples pasos, debe usar una instrucción let . Entonces, ingrese let y pegue el código del Editor avanzado.

Agregar varias hojas en Excel a LuckyTemplates

Luego, cambie Bond_Sheet a Data para transformar la tabla en la columna Data.

Agregar varias hojas en Excel a LuckyTemplates

Después de presionar OK, se agregará una nueva columna en la tabla.

Agregar varias hojas en Excel a LuckyTemplates

Si hace clic en el espacio en blanco dentro de cualquier celda de esa columna, verá los datos de la consulta Bond(2).

Agregar varias hojas en Excel a LuckyTemplates

Todo lo que necesita está en las dos últimas columnas de la consulta de bonos. Por lo tanto, seleccione las columnas Security Ticker y Custom y haga clic en Remove Other Columns de la cinta Remove Columns.

Agregar varias hojas en Excel a LuckyTemplates

Expanda la columna Personalizado y desmarque la casilla Usar el nombre original como prefijo.

Agregar varias hojas en Excel a LuckyTemplates

Una vez hecho esto, valide la tabla. Debajo de la tabla, seleccione la opción Generación de perfiles de columna basada en todo el conjunto de datos .

Agregar varias hojas en Excel a LuckyTemplates

Después de esa transformación, se producirá un error en la columna Datos.

Agregar varias hojas en Excel a LuckyTemplates

Por lo tanto, examine el error haciendo clic en la columna Datos y seleccionando Mantener errores en la cinta Mantener filas.

Agregar varias hojas en Excel a LuckyTemplates

Si hace clic en el valor de Error dentro de la columna Datos, puede leer el siguiente mensaje de error:

Agregar varias hojas en Excel a LuckyTemplates

Para resolver los problemas, primero elimine el paso Errores guardados. Haga clic en la columna Datos y seleccione Eliminar errores en la cinta Eliminar filas.

Agregar varias hojas en Excel a LuckyTemplates

Luego, vuelva a establecer la opción de creación de perfiles de columna en las primeras 1000 filas. ¡Y eso es!

Agregar varias hojas en Excel a LuckyTemplates

Conclusión

Esta solución basada en la interfaz de usuario puede ayudarlo a agregar varias hojas de un archivo de Excel a LuckyTemplates. En lugar de crear 60 consultas separadas y realizar todas las transformaciones repetidamente, esta solución puede crear una única consulta que realiza todas las transformaciones. Utilice y maximice esta solución para crear un excelente informe de datos .

Toronjil


Time Intelligence en LuckyTemplates: cómo calcular el número de transacciones realizadas en los últimos N días

Time Intelligence en LuckyTemplates: cómo calcular el número de transacciones realizadas en los últimos N días

Descubra cómo calcular el total de transacciones realizadas en los últimos N días y obtenga información útil utilizando la inteligencia de tiempo en LuckyTemplates.

Cómo evaluar clústeres en sus datos mediante la técnica DAX en LuckyTemplates

Cómo evaluar clústeres en sus datos mediante la técnica DAX en LuckyTemplates

En este tutorial, demuestro cómo mostrar clústeres en sus datos permite extraer información valiosa usando técnicas DAX avanzadas.

¿Cuánto personal tenemos actualmente? Lógica de fechas múltiples en LuckyTemplates usando DAX

¿Cuánto personal tenemos actualmente? Lógica de fechas múltiples en LuckyTemplates usando DAX

En este tutorial, aprenderá a calcular la <strong>población actual del personal</strong> para las relaciones múltiples de la tabla de calendario mediante la función COUNTROWS y FILTER en LuckyTemplates.

Hacer una solicitud HTTP en Power Automate

Hacer una solicitud HTTP en Power Automate

Aprenda a crear una solicitud HTTP con Power Automate y cómo implementar soluciones efectivas para la automatización de tareas.

Programe un flujo de escritorio para que se ejecute en Power Automate

Programe un flujo de escritorio para que se ejecute en Power Automate

En este tutorial, aprenda cómo programar un flujo de escritorio en Power Automate para automatizar tareas en la web y su escritorio.

Power Automate Terminar Control de acción en flujos

Power Automate Terminar Control de acción en flujos

Aprenda a usar correctamente el control Terminar acción de Power Automate que termina las acciones en un flujo si no se cumple alguna de las condiciones.

Power Query: combinar archivos de varias carpetas

Power Query: combinar archivos de varias carpetas

Aprenda a combinar archivos de varias carpetas en su red, escritorio, OneDrive o SharePoint utilizando Power Query.

Tipos de flujo de Power Automate y cuándo usarlos

Tipos de flujo de Power Automate y cuándo usarlos

En este blog, analizamos las similitudes y diferencias entre los tipos de flujo de Power Automate y determinamos cuándo usar cada flujo. Encuentra la mejor opción de automatización.

Conectores de Power Automate: número, texto y fecha y hora

Conectores de Power Automate: número, texto y fecha y hora

Familiarízate con algunos conectores Power Automate integrados que transforman cadenas en otro formato: número, texto y fecha y hora.

Tutorial del analizador VertiPaq: relaciones e integridad referencial

Tutorial del analizador VertiPaq: relaciones e integridad referencial

Este tutorial detallado sobre el analizador VertiPaq en DAX Studio le enseñará cómo usar la pestaña Relaciones para optimizar su DAX y resolver problemas de integridad referencial.