martes, 22 de diciembre de 2020

Power Query: Optimizar la opción Desde Carpeta

Seguramente todos en algún momento de nuestro trabajo hemos necesitado implementar una consulta de Power Query que recupere toda la información contenida en una carpeta.
Desde Power Query es sencillo (puedes ver cómo aquí)

Lo que todos hemos comprobado, además de la facilidad, es que (como punto negativo) nos genera un montón de consultas intermedias, parámetros o funciones que luego muestra en el panel de consultas, y nos 'ensucia' la limpieza visual que necesitamos para otras consultas...
Power Query: Optimizar la opción Desde Carpeta

Hoy veremos cómo podemos personalizar y automatizar este proceso de importar datos desde una carpeta para dejarlo únicamente en una consulta y una función.

Como antecedentes comentar que mis ficheros a anexar se encuentrar en una ruta: F:\excelforo\pq_memoria
que además para que sea más personalizable he escrito en la celda I2, a la cual he asignado como nombre definido 'ndRUTA'.
Asi que accedemos al editor de Power Query creando una Consulta en blanco.
Desde la ficha Datos > grupo Obtener y Transformar > Obtener datos > Desde otras fuentes > Consulta en blanco.
Entonces desde el Editor avanzado escribiremos y crearemos la siguiente función, que llamo: fxGetFolder:
let
    // tomamos la ruta de la celda...
    ruta=Excel.CurrentWorkbook(){[Name="ndRUTA"]}[Content]{0}[Column1],

    // el primer fichero encontrado será el de ejemplo.
    ArchivoEjemplo=Folder.Files(ruta){0}[Content],

    // damos como valor el Archivo de Ejemplo y lo definimos como un parámetro real
    param=ArchivoEjemplo meta [IsParameterQuery=true, BinaryIdentifier=ArchivoEjemplo, Type="Binary", IsParameterQueryRequired=true],

    Origen = 
        (param as binary) => let
            // para cargar siempre primera hoja.. sea cual sea su nombre!
            Origen1 = Excel.Workbook(param, null, true){[Item=Excel.Workbook(param, null, true){0}[Item],Kind="Sheet"]}[Data],
            // promovemos la primera fila de cada fichero como encabezado
            EncabPromovidos=Table.PromoteHeaders(Origen1, [PromoteAllScalars=true])
            in
            EncabPromovidos
in
    Origen

Power Query: Optimizar la opción Desde Carpeta

Por otra parte, generamos una nueva consulta en blanco, que llamaré 'fuente_CARPETA' donde añadiremos las siguientes instrucciones:
let
    path=Excel.CurrentWorkbook(){[Name="ndRUTA"]}[Content]{0}[Column1],
    ArchivoEjemplo=Folder.Files(path){0}[Content],
    Origen = Folder.Files(path),
    
// si tuvieramos dudas de si existen ficheros ocultos en la carpeta dejaríamos activa la siguiente línea.
//    ArchivosOcultosFiltrados = Table.SelectRows(Origen, each [Attributes]?[Hidden]? <> true),
    AddCol_IncorporamosFicheros = Table.AddColumn(Origen, "Transformar archivo",  each fxGetFolder([Content])),

    // expandimos las columnas de los ficheros
    ExpandeCols = Table.ExpandTableColumn(AddCol_IncorporamosFicheros, "Transformar archivo", Table.ColumnNames(fxGetFolder(ArchivoEjemplo))),

    // Eliminamos columnas de información de los ficheros, que no necesitamos
    ColumnasQuitadas = Table.RemoveColumns(ExpandeCols,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"})
in
    ColumnasQuitadas

Power Query: Optimizar la opción Desde Carpeta
Al final, lo importante, ejecutemos la consulta generada por el asistente o por nuestro proceso 'personalizado', tendremos lo mismo:
Power Query: Optimizar la opción Desde Carpeta

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.