Saludos amig@s
Antes de empezar nuestra etapa de Modelación y Análisis, en cualquiera de las herramientas que tenemos a disposición para crear soluciones en Inteligencia de Negocios ya sea en Microsoft Excel o Power BI a través del Lenguaje DAX, es necesario tener en cuenta que los datos a analizar, probablemente debamos obtenerlos de diferentes fuentes como por ejemplo: archivos de texto, archivos de Excel, un gestor de bases de datos, etc.
También, es muy probable que dichos datos debamos limpiarlos y transfórmalos, por eso hoy continuaremos explorando diversas aplicaciones de Power Query (también conocido como obtener y transformar en Excel 2016 ) para aplicar un formato tabular a una “tabla” que no está preparada para ser utilizada como fuente de alimentación de una tabla dinámica o simplemente de ser agregada al modelo de datos.
Ejemplo – Desempeño de Vendedores
Vamos a suponer que somos líderes del equipo comercial de una fábrica de Tiendas para Acampar y para analizar el desempeño de los vendedores en el mes de Julio, debemos preparar y limpiar la siguiente tabla proporcionada por el equipo de gestión de la información de nuestra compañía.
Nota: La tabla tiene aproximadamente 100 registros, con toda la información relacionada a las unidades vendidas, dependiendo del Agente Comercial asignado. Recordemos que el IVA lo debe pagar el cliente por lo que se le debe agregar al valor final que debe pagar por la carpa.
Fácilmente, podemos ver que el rango de datos no tiene un formato tabular, el necesario para ser fuente de alimentación de una tabla dinámica.
Características de una tabla con formato tabular:
- Etiquetación de Columnas
- No subtotales Ni totales
- No columnas Ni Filas Vacías
Aunque pueda parecer compleja esta situación, sabemos que nuestro mejor aliado para Extraer, Transformar y Cargar datos en Excel, es Obtener y Transformar como también es conocido en Power BI Desktop.
Proceso Importar – Transformar – Cargar (Extract – Transform – Load ) ETL con Power Query
1. Importar (Extract)
Fácilmente podemos observar que el rango de datos que está en la hoja Tabla de Datos no es una Tabla Estructurada de Excel, ya que no se habilita la pestaña Herramientas de Tabla al situar la celda activa sobre dicha “tabla”, esto para aclarar que vamos a tratar con rangos de datos.
rBueno pero no nos desviemos, el primer paso siempre será la Extracción de los datos, por lo que en el mismo archivo, vamos a la pestaña Datos, grupo Obtener y Transformar, y damos clic en la opción, desde una tabla.
En el cuadro de dialogo Crear Tabla, vamos a elegir el rango que contiene todos los datos del mes de julio.
Nota: Intencionalmente tomamos las dos primeras filas como parte de la tabla.
Dejamos deshabilitada la opción La tabla tiene encabezados y damos clic en Aceptar.
Y automáticamente se “cargan” los datos en el Editor de Consultas y el rango de datos se convierte en una Tabla Estructurada de Excel.
2. Transformación (Transform)
Con los datos “cargados” en el Editor de Consultas, ha llegado el momento de transformarlos …
Quitar Filas Superiores
En el Editor de Consultas, damos clic en el botón Reducir Filas, luego seleccionamos el comando Quitar Filas y por ultimo elegimos la opción Quitar Filas Superiores.
Y en el cuadro de dialogo Quitar Filas Superiores, escribimos el número de filas que deseamos quitar, contando de arriba hacia abajo, para este ejemplo 2 y para finalizar, damos clic en Aceptar.
Usar primera Fila como encabezado
En la pestaña Transformar, nos dirigimos al grupo Tabla -> allí damos clic en el comando Usar la primera fila como encabezado.
Y aplicando estos pasos en la consulta, obtenemos el siguiente resultado:
Ahora bien, debemos continuar con la transformación.
Quitar Filas de la consulta través del Filtro
Sabemos que en medio y al final de la tabla de datos existen dos filas que contienen el sub totales con respecto a las unidades vendidas en las dos zonas de la ciudad (Norte y Sur), por lo que debemos eliminarlas.
Damos clic en el botón de filtro, que aparece en la etiqueta de la columna en la cual vamos a quitar las filas -> En las opciones de filtro que aparecen, vamos a inhabilitar las filas que contienen los ítems: TOTAL UNIDADES ZONA NORTE y TOTAL UNIDADES ZONA SUR -> para finalizar damos clic en Aceptar.
Al dar clic en Aceptar, se quitan dichos ítems de la consulta, puedes observar en la barra de estado que efectivamente se han quitado las filas con los totales generales.
Nota: Pasamos de 86 filas a 84 filas, recordemos que en el Panel de Resultados (Área del editor de consultas, donde se muestra la tabla retornada por la consulta, en el paso actual), no podemos ver todos los datos que provienen del origen si se cuenta con una fuente de datos muy grande.
Las celdas vacías que aparecen el origen de datos, son representadas en Power Query como un Valor nulo ( Null), todo con el fin de facilitar el trabajo de manipulación de columnas o filas, que tengan este valor.
Habiendo aclarado lo anterior, vamos a terminar de preparar nuestros datos.
Rellenar Columnas
En lugar de dejar las celdas vacías, tenemos que rellenarlas repitiendo el valor de arriba hacia abajo, en ese orden de ideas:
Seleccionamos la columna a rellenar (clic izquierdo sobre la etiqueta) -> Pestaña Transformar -> grupo Cualquier Columna -> Damos clic en el comando Rellenar (el botón con un icono de tabla y una flecha hacia abajo) ->elegimos la opción Rellenar.
y Bang !!
Mira lo que ha pasado con la columna:
lFantástico no te parece, rápidamente se rellena de arriba hacia abajo las celdas vacías con el valor de la celda vecina que está por encima.
Debemos repetir este procedimiento en todas las columnas y el resultado es el siguiente:
La tabla no tendrá valores nulos, sino que por el contrario contiene cada ítem especifico en la columna.
Reemplazar Valores
Si nos fijamos un segundo en la Columna Valor/Unidad, observamos que el separador de miles en esta columna es el (punto) y no una coma.
Es decir que el valor de Camping Normal no es de $ 250.000 (Doscientos cincuenta mil dolares) sino $250, (Doscientos cincuenta dólares).
-Una pequeña diferencia …
Veamos un segundo que pasa si no intercambiamos el separador de miles y luego elegimos el tipo de dato Moneda:
Como era de esperarse, la integridad de los datos ha sido afectada, puesto que se han cambiado los valores reales por unos erróneos.
Por es razón, debemos tener esta idea muy presente.
Ahora bien, esto no es ningún problema con Power Query …
Quitar un Paso (Transformación) en la consulta.
Para quitar un Paso, en la consulta:
En el editor de consultas nos dirigimos al Panel de Configuración de la Consulta -> en la Sección de Pasos aplicados -> Damos clic en la X que aparece al lado izquierdo del paso a eliminar.
Y así de simple, hemos eliminado el paso Tipo de dato cambiado.
Reemplazar Valores
Ahora bien, retomando el procedimiento, debemos reemplazar el carácter punto por la coma en todas las columnas con valores de tipo número:
Clic derecho sobre la etiqueta de la columna -> Reemplazar los Valores.
Nota: En la configuración de mi computador, el carácter separador de decimales es la Coma ( , ) y el Punto ( . ) es el carácter encargado de ser separador de Miles y allí radica la razón del por qué reemplazar estos valores.
En el cuadro de dialogo Reemplazar los Valores que aparece automáticamente, asignamos los valores que debemos intercambiar:
- Valor que Buscar: Es el Valor que debemos buscar en cada celda de la columna y que va a ser reemplazado, en este caso el carácter punto ( . )
- Reemplazar con: Es el valor que va a ser asignado en todas las celdas de la columna, en este caso el carácter coma (,)
Para finalizar, damos clic en Aceptar.
Como puedes ver en la imagen anterior, en TODAS las celdas de la columna se ha intercambiado exitosamente el carácter.
TNuevamente repetimos el procedimiento, para las columnas restantes.
Nota: si queremos reemplazar el mismo carácter en varias columnas, podemos hacerlo, seleccionando las columnas con CTRL y luego repetimos el procedimiento Reemplazar los valores.
Para finalizar, vamos a pasar columna por columna asignando el tipo de dato según su naturaleza.
Asignar Tipo de Dato
Para Modificar el Tipo de Dato de Varias columnas:
Con CTRL presionado, damos clic izquierdo sobre las etiquetas de las columnas que vamos a asignar con un mismo Tipo de Dato, para este ejemplo las columnas: Vendedor , Numero DOC , Cliente y Producto -> clic derecho sobre alguna etiqueta seleccionada -> damos clic sobre el comando Cambiar Tipo -> elegimos la opción Texto.
Repetimos el procedimiento, para las demás columnas:
Cambiar Tipo de Dato con la Cinta de Opciones
Seleccionamos la etiqueta de la columna para este caso, Valor / Unidad -> en la pestaña Inicio del Editor de Consultas nos dirigimos al grupo Transformar -> en las opciones de Tipo de Dato elegimos Moneda.
Adicional a ello, cambiamos el tipo de dato a Número Decimal, de las últimas dos columnas.
Para finalizar, vamos a modificar el nombre de la consulta por uno más descriptivo.
En el panel de configuración de consulta nos dirigimos al cuadro de texto Nombre -> reescribimos el nombre de la consulta, para este caso Reporte_Vendedores_Julio.
3. Carga (Load)
Para cargar la tabla obtenida a través de la consulta en una hoja del libro, como una tabla estructurada de Excel, damos clic en la opción Cerrar y Cargar, en el Editor de Consultas.
Automáticamente se carga en una hoja nueva del libro, una tabla estructurada de Excel con los datos limpios y transformados, listos para alimentar una tabla dinámica o cualquier solución en Excel, recuerda que también podemos cargar los datos directamente en el modelo de datos o simplemente crear la conexión con los datos, para luego utilizarlos, pero más adelante profundizaremos en ello, por el momento veamos la tabla:
Nota: En el Panel de consultas del libro, podemos observar las consultas que se están utilizando en el libro actual.
Con estas simples acciones hechas en Power Query, podemos limpiar grandes cantidades de datos fácil y rapidamente, incluso existen procedimientos para automatizar esta tarea. Pero bueno por hoy es todo, espero que sea de mucha utilidad esta publicación, hasta la próxima.