Real ETL Process with PowerQuery

Escenario

En un proyecto de construcción de gran envergadura, el PM recibe semanalmente los avances en forma de archivos Excel. Estos archivos son extremadamente complejos, con múltiples hojas y tablas que a menudo carecen de una estructura tabular.

La necesidad de realizar cálculos específicos implica la extracción de información precisa de estos archivos Excel.

Objetivo

Nuestro objetivo es desarrollar un proceso ETL que pueda extraer datos de los archivos Excel mencionados y, además, actualizarse de forma automática para mantener la información al día. Este proceso también debe ser capaz de almacenar los datos en un formato tabular dentro de nuestra base de datos.

Dentro de cada archivo Excel, en hojas específicas, se encuentran definidos rangos de celdas que contienen los porcentajes correspondientes a las diferentes fases del proyecto en el sitio de construcción.

Por ejemplo, en el archivo "NombreVariable.xlsx", en la hoja de cálculo "ConstructionZone2941", los pesos clave están definidos en el rango de celdas que va desde la columna G hasta la columna Q y en la fila 44.

Estos pesos son fundamentales para calcular el progreso agregado del proyecto, y son el objetivo principal de nuestra extracción de datos.

Premisas

Después de una exhaustiva reunión de requerimientos con el PM, hemos establecido que el proceso debe cumplir con los siguientes criterios:

1. Los archivos Excel pueden tener cualquier nombre y contener un número variable de hojas de cálculo.
2. Los nombres de las hojas de cálculo de las cuales se extraerá información están definidos en nuestra base de datos. Estos nombres corresponden a los códigos de los sitios de construcción.
3. La información que se necesita extraer está definida en un rango específico de columnas y filas dentro de cada hoja de cálculo.

Proceso general:

Para realizar el proceso utilizarmeos las siguientes tecnologías:

• DataFlow: Basado en PowerQuery, permite definir un proceso ETL facilmente integrable en distintas plataformas como Microsoft Fabric, Power BI Service, DataVerse...
• PowerAutomate: Muy similar a un Pipeline que permite monitorear la creación de archivos, desencadenar el proceso ETL y notificar al Owner en caso de errores.
• DataVerse: Integrado con PowerPlatform permite definir una tabla como Output de un DataFlow.
• SharePoint: Sitio de almacenamiento, permite organizar los archivos en formato no estructurado, accesible por distintos usuarios sin licencias adicionales.

Proceso detallado

1. Creación del Dataflow: Comenzamos por crear un Dataflow en la plataforma seleccionada. Este Dataflow será el encargado de gestionar la extracción, transformación y carga de datos desde los archivos Excel en SharePoint hacia nuestra base de datos.
2. Definición de la Ruta en SharePoint: Especificamos la ruta en SharePoint donde se encuentran almacenados los archivos Excel que queremos procesar. Esto asegurará que el Dataflow acceda a la ubicación correcta para obtener los datos.
3. Creación de Tablas: Ahora, creamos dos tablas dentro del Dataflow:La primera tabla se conectará a una tabla específica en nuestra base de datos(NISAssets). Esta tabla nos proporcionará el listado de sitios de construcción, que luego utilizaremos para filtrar las hojas de cálculo de los archivos Excel. La segunda tabla (Folder_Excels) estará compuesta por los archivos Excel ubicados en la carpeta de SharePoint. Para lograr esto, nos conectamos a SharePoint y filtramos los archivos por su ruta ("Path") y por el tipo de archivo (".xlsx").

Perfecto, ahora nos enfocaremos en dos aspectos clave:

1. Iteración sobre cada Excel para acceder a determinadas hojas.
2. Extracción de un rango específico de datos en cada hoja.

Para lograr esto, crearemos funciones personalizadas en PowerQuery que nos permitirán automatizar estos procesos.

Función para Iterar sobre cada Excel y Acceder a Determinadas Hojas:

(InputFile as binary, Assets as list) =>
let
    Source = Excel.Workbook(Binary.Buffer(InputFile)),
    #"Filtered Rows" = Table.SelectRows(Source, each List.Contains(Assets,[Item]))
in
    #"Filtered Rows"        

Función para Iterar sobre cada Hoja y Acceder a un rango específico:

(Sheet as table) =>
let
    #"Kept Range of Rows" = Table.Range(Sheet, 43, 1)
in
    #"Kept Range of Rows"        

Ahora, aplicaremos dichas funciones en su orden correspondiente y después de arreglos menores como cambiar nombres, definir tipo de columnas etc.

Nos quedamos con un esquema como el siguiente:

• NISAssets: Listado de códigos para filtrar.
• Get_Sheets: Función para iterar sobre Excels.
• Get_Table: Función para iterar sobre Hojas.
• Folder_Excels: Listado de Excels en carpeta contenedora
• Folder_Sheets: Listado de hojas agrupadas desdespués de aplicar la función "Get_Sheets"
• Weights: Tabla final consolidada después de aplicar la función "Get_Tables"

La salida en formato tabular del DataFlow, la definiremos como nueva tabla en DataVerse:

Para asegurar que nuestro proceso ETL se ejecute automáticamente cada vez que se cree un nuevo archivo Excel en la carpeta especificada, lo programaremos para que sea activado por un flujo de Power Automate.

Consideraciones finales:

• Parte de un Proceso Más Grande: Es importante recordar que este proceso ETL es solo una parte de un flujo de trabajo más amplio. Pueden existir otros procesos ETL adicionales para organizar rutas y mover archivos, así como para realizar consultas específicas con los datos obtenidos en un modelo dimensional más grande.
• Control de Errores y Limpieza de Datos: Aunque no hemos detallado los procesos de control de errores y limpieza de datos en este ejemplo, es esencial incorporar estas prácticas en cualquier proceso ETL. Esto incluye establecer mecanismos para manejar errores, limpiar y validar los datos de entrada, y establecer notificaciones en caso de fallos o inconsistencias en los datos.
• Licenciamiento: El licenciamiento del proceso ETL en sí mismo es gratuito, pero pueden requerirse licencias para las bases de datos utilizadas (como Dataverse o SQL) y las licencias de las herramientas finales (como Power BI). Sin embargo, en general, este enfoque puede ser beneficioso para empresas de cualquier tamaño y presupuesto, ya que proporciona una solución eficiente y escalable para la gestión de datos. Por ejemplo, este mismo proceso es perfectamente integrable para trabajar con cientos de archivos y distintas fuentes de datos en Microsoft Fabric.

Un abrazo.