Rendimiento de Power Query con funciones personalizadas aplicadas a libros de Excel

Hola,

Me encuentro con un problema de rendimiento grave al utilizar Power Query en las siguientes circunstancias:

Estoy tratando de obtener los datos de 263 libros de Excel cargados en el modelo de datos (263 no es un número fijo, pero no espero grandes variaciones; digamos entre 200 y 400).

Hay cuatro «regiones» de datos distintas en los libros de trabajo que he denominado «Detalles», «Finanzas», «Carga de trabajo» e «Ingresos» y mi intención es sacar cada una de ellas por separado y luego agregarlas al modelo de datos. con las relaciones entre ellos. Solo he descrito el proceso para uno de ellos a continuación, pero todos son básicamente iguales; son solo diferentes áreas de los libros de trabajo que se están extrayendo.

Para comenzar, hago referencia a la plantilla de libro de Excel que se ha utilizado para crear todos los demás como este:

dejar
Source = Folder.Files («\ carpeta del servidor»),
# «\ carpeta del servidor _nombre de la plantilla xlsm1″ = Fuente {[#»Folder Path»=»\server folder», Name = «template name.xlsm»]}[Content]
en
# «\ nombre de plantilla xlsm1»

Luego creo un parámetro con un tipo de binario y el valor predeterminado del archivo de plantilla. Yo llamo a esto el parámetro de plantilla

Con esto, puedo crear la primera de mis consultas de la siguiente manera:

dejar
Fuente = Excel.Workbook (# «Parámetro de plantilla», nulo, verdadero),
Navegación = Fuente {[Name=»Resources»]}[Data],
# «Otras columnas eliminadas» = Table.SelectColumns (Navigation, {«Column3», «Column4»}),
# «Intervalo de filas mantenido» = Table.Range (# «Otras columnas eliminadas», 3,9),
# «Removed Blank Rows» = Table.SelectRows (# «Intervalo de filas mantenido», cada uno no List.IsEmpty (List.RemoveMatchingItems (Record.FieldValues (_), {«», null}))),
# «Tabla transpuesta» = Table.Transpose (# «Filas en blanco eliminadas»),
# «Encabezados promocionados» = Table.PromoteHeaders (# «Tabla transpuesta», [PromoteAllScalars=true])
en
# «Encabezados promocionados»

Luego creo una función con esto y la llamo fnDetails

Obtengo una lista de todos los archivos de Excel usando Folder.Files y llamo a esta Lista de archivos.

Por último, puse los dos juntos de la siguiente manera:

dejar
Fuente = # «Lista de archivos»,
# «Función personalizada invocada» = Table.AddColumn (Fuente, «fnProjectDetails», cada fnProjectDetails ([Content])),
# «Errores eliminados» = Table.RemoveRowsWithErrors (# «Función personalizada invocada», {«fnProjectDetails»}),
# «Expanded fnProjectDetails» = Table.ExpandTableColumn (# «Errores eliminados», «fnProjectDetails», {«LOS NOMBRES DE LAS COLUMNAS SE EXPANDEN»})
en
# «FnProjectDetails expandido»

Todo esto parece que debería funcionar. Desafortunadamente, aunque funciona, puede llevar varias horas cargar los datos, lo cual es básicamente inútil. ¡Sería más rápido abrirlos todos y escribir la información!

El Administrador de tareas no muestra que la CPU, la memoria o el disco hagan algo inusual, y la red tiene mucha capacidad de reserva en comparación con lo que realmente se está utilizando.

Estoy seguro de que estoy malinterpretando algo, pero varias semanas de lectura y experimentación no me han ayudado a superar este problema.

Gracias,

Chris

Si puede proporcionar un enlace a dos archivos simulados, se puede proporcionar una consulta que los combine como ejemplo. Si no definió tablas para sus rangos de datos (es decir, Formato como tabla o Ctrl-T), entonces tendrá que extraer los datos de toda la hoja. Si es posible, conviértalos en tablas y luego podrá obtener rápidamente el contenido con Kind = Table y Name = Details, etc. una vez que lo abra con Excel.Workbook. Dado que sus archivos ya existen, no puede hacer eso, por lo que deberá continuar con el enfoque actual.

Palmadita

Hola @OuluChris

Tuve los mismos requisitos, pero tuve que leer más de 2k archivos de Excel y puedo decirles que la actualización no toma mucho tiempo. Aqui mi concepto

– Creé una función que toma la ruta del archivo como parámetro y el resultado es una tabla con 3 columnas y 1 fila para 3 conjuntos de datos diferentes (hojas de trabajo)

luego creé una consulta básica que se lee una vez desde la carpeta y aplico mi función en cada archivo. Luego expanda las 3 columnas, rodeándolas con table.buffer

El siguiente paso es crear para cada conjunto de datos una nueva consulta, donde tomo mi consulta básica, borrando la columna no necesaria, expandiendo las columnas de la tabla y en caso de manipular más

Si esta publicación ayuda o resuelve su problema, márquelo como solución (para ayudar a otros usuarios a encontrar contenido útil y reconocer el trabajo de los usuarios que lo ayudaron)
Los kudoes también son agradables

Divertirse

Palanqueta

¿Cuál es el propósito de abrir el archivo de plantilla para cada archivo? ¿Por qué no crear solo 4 funciones, cada una para extraer la tabla de cada libro de trabajo (Detalles, Finanzas, etc.)? Tampoco haría Table.Range y Table.SelectRows en la función, pero lo haría después de que las tablas se combinen de todas las plantillas.

Si necesita algo del archivo de plantilla para cada archivo, lo extraería en su consulta final y lo envolvería en Table.Buffer () para que solo se llame una vez. Si no puede prescindir del archivo de plantilla, usar Table.Buffer será clave para disminuir significativamente el tiempo de actualización (evite hacerlo 4 X 263 veces).

Palmadita

Gracias por tus pensamientos.

Me gusta la idea de no abrir el archivo de plantilla para cada archivo, y no hay nada que necesite de la plantilla, pero no sé cómo escribir la función personalizada sin hacer referencia a ella. Basé lo que tengo hasta ahora en lo que leí aquí: https://docs.microsoft.com/en-us/power-query/custom-function

(No utilicé uno de los archivos que estoy importando como plantilla, ya que estos cambiarán con el tiempo)

Además, no entiendo cómo no pude usar Table.Range y Table.SelectRows en la función. Si no los uso en la función, ¿no termino importando la hoja completa?

Entradas relacionadas

Deja un comentario Cancelar respuesta