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.

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

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
• Sacar el teletipo de seguridad de Excel a LuckyTemplates
• Eliminar filas vacías
• Duplicar una consulta
• Asignación de tipos de datos a columnas
• Adición de la columna de teletipo de seguridad de Excel a LuckyTemplates
• Creación de una columna personalizada para consulta de bonos
• Conclusión

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 .

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.

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

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.

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

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 .

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.

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 .

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 .

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 .

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

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

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

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.

Eliminar filas vacías

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

Esa transformación genera esta sintaxis:

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 .

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

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 .

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.

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

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.

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í.

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

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 .

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 .

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.

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.

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

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.

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.

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

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

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.

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.

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:

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.

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

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

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

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.

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

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 .

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

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

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

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.

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

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