Explorando la funcionalidad Get & Transform de Excel

Resumen ejecutivo

En esta era de lagos de datos y bases de datos a escala de petabytes, es sorprendente la frecuencia con la que sigo recibiendo datos en forma de archivos CSV, de texto y de Excel. Si bien el análisis de hoy en día se enfoca en avances de vanguardia en algoritmos de aprendizaje automático, la monotonía diaria del análisis de datos sigue siendo un proceso manual de búsqueda, compilación y disputa de tipos de datos dispares.

Para el analista financiero, los datos a menudo llegan como una hoja de cálculo de Excel, pero con la misma frecuencia, es un volcado de datos en un CSV o una consulta en una base de datos SQL. A veces, los datos se organizan en un diseño confuso o no tienen todos los componentes necesarios para el análisis. El tiempo dedicado a depurar estos datos es una pérdida de tiempo valiosa para el analista, pero a veces esta tarea se acepta como un mal necesario que se debe tolerar.

¿Qué hace Get & Transform?

En realidad, una solución a este problema común es bastante accesible: Excel y Power BI tienen un conjunto completo de herramientas de transformación de datos que pocos usuarios conocen, llamado Get & Transform (anteriormente conocido como Power Query). El uso de su funcionalidad integrada de extracción, transformación y carga (ETL) permite a los analistas financieros vincularse sin problemas a sus fuentes de datos y obtener información más rápidamente.

A medida que preparamos los datos para cargarlos en Excel o Power BI, generalmente tenemos que realizar algunas transformaciones en los datos. Algunos ejemplos de manipulación de datos incluirían:

• Quitando columnas,
• Filtrando los datos,
• Agrupando los datos,
• Pivotar/despivotar los datos,
• División de cadenas en subcadenas,
• Extraer palabras clave de cadenas,
• Agregar filas de otra tabla, y
• Unión de dos tablas de dimensiones.

En el siguiente diagrama, vemos que Get & Transform realiza esta tediosa función de preprocesar los datos antes de que se carguen.

¿Por qué debería usar Get & Transform?

¿Por qué vale la pena aprender a usar Get & Transform? Bueno, cuando miro para qué he usado personalmente esta funcionalidad, me ha ofrecido un conjunto maleable de herramientas para:

• Cargar una carpeta completa de archivos de texto en una sola tabla de datos
• Conversión de archivos de contabilidad exportados en un diseño digerible
• Cargar millones de filas de ventas en Power Pivot directamente
• Agrupación de datos diarios en resultados mensuales manejables antes de importarlos a Excel
• Empalme de datos de otra tabla uniéndose en columnas coincidentes

Por lo general, cuando recibo datos nuevos, los exploro con Get & Transform antes de cargarlos en Power Pivot. Esto me permite ver qué transformaciones pueden ser necesarias y realizar rápidamente algunos pivotes y agrupaciones en los datos para formular un marco de análisis. En muchos casos, en esta etapa, encontraré que necesito más datos o que hay problemas con los datos. Al usar una plataforma basada en Excel, puedo iterar rápidamente con mi fuente de datos para encontrar estas anomalías de datos.

En última instancia, la decisión de permanecer en Excel o mover el análisis de datos a otra plataforma dependerá de la audiencia y la repetibilidad y distribución del análisis. Si mis clientes solo usan Excel, casi siempre usaré Get & Transform para cargar los datos, Power Pivot para realizar el análisis y Excel para producir tablas dinámicas y gráficos. Para el cliente, esto se sentirá perfecto ya que todo está alojado en Excel.

Sin embargo, si mi cliente:

1. Quiere usar otra herramienta de visualización,
2. Tiene varios usuarios que actualizarán los datos, o
3. Necesita emplear modelos de aprendizaje automático,

Luego usaré Get & Transform únicamente para la exploración de datos inicial y luego pasaré el trabajo pesado a R.

Cómo acceder a Get & Transform en Excel o Power BI

En versiones anteriores de Excel, Power Query era un complemento que se podía instalar para ayudar con las funciones de ETL. Sin embargo, en Excel 2016 y Power BI, estas herramientas están más integradas. En Excel 2016, se puede acceder a ellos a través de la pestaña Datos y luego la sección Obtener y transformar datos .

En Power BI, la funcionalidad existe en la pestaña Inicio , en la sección Datos externos .

En este artículo, mis ejemplos tienen lugar en Power BI, pero la interfaz es casi idéntica a la de Excel. Señalaré las diferencias cuando surjan para que el tutorial tenga sentido para ambos tipos de usuarios.

1. Cargar archivos CSV

Para ayudar en este tutorial, he creado algunos ejemplos de datos de ventas para un minorista ficticio que vende ropa y equipo para actividades al aire libre. En cada uno de estos ejemplos, los datos se producirán de diferentes maneras para demostrar métodos realistas de volcado de datos.

Como ejemplo inicial, veremos los datos presentados como un gran volcado de datos en un archivo CSV. El factor de complicación es que los datos se presentan con múltiples columnas que representan varias tiendas. Idealmente, nos gustaría importar y transformar los datos en un diseño más útil.

A continuación se muestra una captura de pantalla de cómo se ve el CSV sin procesar:

¿Por qué querríamos cambiar esto? Aprovechar las capacidades de relación que son posibles en estas aplicaciones. Veremos este juego más adelante en la discusión.

Por ahora, supongamos que necesitamos ver los datos como una estructura "más estrecha y más alta", en lugar de una estructura "más ancha y más corta". El primer paso es cargar el CSV; luego, comenzaremos a “despivotar” los datos.

Como puede ver, la estructura final de los datos es más estrecha que los datos iniciales y mucho más larga. Otro punto es que, a medida que hacemos clic en diferentes acciones, la herramienta del lado derecho genera una lista de pasos aplicados para construir la consulta. Es importante comprender que esto sucede en segundo plano, ya que se revisará más adelante.

Get & Transform se ve y se comporta de manera similar entre Power BI y Excel en su mayor parte. Sin embargo, en Excel, después de hacer clic en Cerrar y cargar , hay un aviso adicional. En la figura a continuación, podemos alternar entre si deseamos cargar los datos en:

1. Una tabla en Excel,
2. Una tabla dinámica creada contra los datos,
3. Un gráfico dinámico creado contra los datos, o
4. "Solo crea una conexión".

Además, también se nos da la opción de agregar o no estos datos al modelo de datos . Al marcar esta casilla, se cargan los datos en una tabla de Power Pivot. Si vamos a analizar los datos en Power Pivot, aconsejo elegir Solo crear una conexión y luego asegurarse de que la opción Agregar estos datos al modelo de datos esté seleccionada. Si los datos están dentro del límite de filas de Excel y preferimos realizar nuestro análisis en Excel, simplemente seleccione Tabla .

En el siguiente clip, veremos que la razón por la que formateamos los datos para que sean largos y delgados es para que podamos analizar las ventas no solo por tienda sino también por región y estado. Para realizar esta tarea, importaremos una tabla que asigna cada tienda a una región y estado. Veremos a continuación que podemos crear rápidamente informes que muestren las ventas por estos diferentes grupos.

Puede imaginar cómo este tipo de capacidad para la transformación de datos en Excel, o Power BI, se puede aplicar poderosamente a cualquier caso en el que tengamos agrupaciones dinámicas de datos, como:

• Acumulación de datos diarios en semanas, meses y trimestres;
• Agrupar al personal de ventas en departamentos y regiones; o
• Asignación de SKU a tipos de productos.

Si bien este artículo aborda CSV y otros archivos de Excel, Get & Transform aborda una amplia gama de tipos de datos. Una vez que se crea una consulta, se puede actualizar con el tiempo a medida que cambian los datos.

2. Manejo de cadenas de texto

Para demostrar la capacidad de Get & Transform para manipular cadenas, creé otro conjunto de datos que imita un archivo de texto que muestra las transacciones contables del libro mayor (GL) de una empresa.

¿Observa cómo el número de cuenta y el nombre aparecen en la misma cadena? En Power BI, podemos analizar sin esfuerzo el número de cuenta y el nombre en campos separados.

En este video, puede ver que después de dividir la columna, la herramienta adivinó que el nuevo lado izquierdo del campo Cuenta debería ser un número, y crea un paso de "Tipo modificado 1". Dado que finalmente queremos este campo como una cadena, podemos continuar y eliminar el paso manualmente en los pasos aplicados.

A continuación, tomamos los mismos datos y creamos un plan de cuentas con asignaciones a categorías de cuentas.

¿Por qué seguiríamos todos esos pasos para mapear algunos números de cuenta? Un libro mayor real puede tener cientos o incluso miles de cuentas. Esta consulta de mapeo rápido, como hemos mostrado, escalaría a ese nivel sin trabajo adicional.

3. Trabajar con diferentes fuentes de datos

Get & Transform admite muchas fuentes de datos diferentes. Si bien no es una lista exhaustiva, a continuación se muestran algunos ejemplos:

Personalmente, solo probé aproximadamente la mitad de las conexiones en la lista anterior. Cada uno de los conectores que he usado ha sido bastante robusto; He pasado de datos sin procesar a conocimientos sin una gran cantidad de trabajo. Lo que es igualmente importante, sirve como un validador entre fuentes de datos dispares, asegurando que los resultados finales tengan un nivel normalizado de control de calidad.

4. Personalización del código con el lenguaje M

En segundo plano, Get & Transform genera código cada vez que hacemos clic en un botón de la herramienta o hacemos una selección. A continuación se muestra un ejemplo de cómo accedería al código para la consulta de asignación de cuenta que creamos:

El código usa un lenguaje funcional llamado M, que se genera automáticamente para casos de uso básicos. Sin embargo, para disputas de datos más complicadas, podemos editar y escribir nuestro propio código. En la mayoría de los casos, solo haré modificaciones menores a este código. En transformaciones más complicadas, es posible que escriba la mayor parte del código desde cero para organizar tablas temporales o para realizar uniones más complicadas.

Los límites de obtener y transformar

Excel tiende a alcanzar sus límites cuando intenta exportar más de un millón de filas. En los casos en los que he transformado millones de filas con Get & Transform, la única forma de enviar filas desagrupadas es a través de tediosos trucos o soluciones alternativas. También descubrí que las consultas Get & Transform pueden ser inestables para implementar en varios usuarios, especialmente si usa varias fuentes de datos y uniones. En esos casos, siempre usaré R para implementar la disputa de datos duplicables. Finalmente, Excel no está diseñado para un modelado de datos más avanzado. Puede realizar regresiones lineales bastante rápido, pero más allá de eso, necesitará usar una plataforma más rigurosa.

Habiendo dicho todo eso, encuentro que Excel es con lo que la mayoría de mis clientes se sienten más cómodos. Excel sigue siendo la herramienta más importante en el arsenal de un analista financiero. Al incorporar la funcionalidad Get & Transform, Excel y Power BI se vuelven aún más potentes a través de la gama de fuentes de datos que pueden aceptar.