Saludos amigas y amigos de Excel Free Blog
Cuando trabajamos con Múltiples tablas para crear reportes o soluciones en Excel, podemos identificar que tienen ciertos problemas en común, afortunadamente, Power Query lo sabe y por ello tiene una gran variedad de comandos para resolver problemas de transformación.
Hoy, exploraremos algunos comandos del editor de Consultas de Power Query, así que empecemos
Ejemplo – Productos de Supermercado
Imaginemos que, debemos crear un reporte relacionado a los productos de un supermercado, ahora bien, el problema no radica en la creación del reporte sino, en el origen de datos…
Como puedes ver, se trata de un rango de datos en Excel (Diferente de una Tabla Estructurada) que no tiene un formato tabular apropiado, por ejemplo, Hay filas en blanco, la información de dos columnas se encuentra en una sola, entre otros problemas que resultan un poco molestos.
Empecemos por quitar las líneas de cuadricula de nuestra hoja de Excel, deshabilitando dicha opción.
Después, seleccionamos el Rango de datos que va desde la celda B2 hasta F76, en este caso.
Luego, presionamos la combinación de teclas CTLR + T, para convertir el rango en una Tabla Estructurada.
En el cuadro de dialogo Crear Tabla, deshabilitamos la opción La tabla tiene encabezados y damos clic en el botón Aceptar.
Así hemos convertido el rango de datos en una tabla estructurada, aunque falta cambiar el nombre:
Pestaña contextual Diseño -> Propiedades -> Damos doble clic sobre el cuadro de texto Nombre de Tabla y lo Reescribimos, en este caso BD_ProductosSuper y damos Enter
En consecuencia, tenemos nuestra Tabla Estructurada.
Luego de estos ajustes que debemos hacer en el origen si trabajamos con rangos de Excel, empecemos con el proceso ETL (Extract, Transform , Load)
Extraer
Para conectarnos con la tabla estructurada, en la pestaña Datos, grupo Obtener y Transformar, damos clic en el comando Desde una Tabla, con la celda activa en la Tabla.
Automáticamente aparece el editor de consultas de Power Query.
Como sabemos la tabla no tiene un formato tabular apropiado y eso se refleja en el Panel de Resultados
Transformación
Para limpiar nuestra Tabla, vamos a utilizar ciertos comandos del editor de consultas …
Podemos utilizar el Editor de Consultas, para Quitar filas por su posición en la tabla y no por el valor que contiene la celda.
Quitar Filas Superiores
Pestaña Inicio -> grupo Reducir Filas -> comando Quitar Filas -> opción Quitar Filas Superiores
En el cuadro de dialogo que aparece, definimos el número de filas que queremos quitar de la parte superior de la tabla y por último damos clic en el Botón Aceptar
Nota: Puedes ver en el Panel de Configuración de la consulta que se ha Aplicado un paso, correctamente
Quitar Filas Inferiores
Pestaña Inicio -> grupo Reducir Filas -> comando Quitar Filas -> opción Quitar Filas Inferiores
En el cuadro de dialogo Quitar Filas Inferiores, especificamos el número de filas a eliminar de la tabla y clic en Aceptar
Usar la Primera Fila como Encabezado
Podemos utilizar la primera fila de una tabla, como las etiquetas de cada columna:
Pestaña Transformar -> grupo Tabla -> comando Usar la Primera Fila como Encabezado y clic sobre el icono
Quitar Filas En Blanco
Si la tabla contiene filas con valores en blanco, en posiciones aleatorias de la tabla podemos eliminarlas fácilmente de dos maneras:
- Filtro Automático:
Damos clic sobre el botón Opciones de Filtro->
En la lista de valores, deshabilitamos la opción (Nulo)
Finalmente, clic en el Botón Aceptar.
Quitar Filas en Blanco
Si tienes versiones recientes de Power Query, cuentas con un comando diseñado especialmente para quitar filas en blanco.
Pestaña Inicio -> grupo Reducir Filas -> comando Quitar Filas -> opción Quitar Filas en Blanco
Quitar Duplicados
Para quitar los valores duplicados que tiene una tabla:
Seleccionamos la primera columna (Contiene los Productos Duplicados) -> Pestaña Inicio -> grupo Reducir Filas -> comando Quitar Filas -> opción Quitar Duplicados
En consecuencia, se redujo a la mitad la cantidad de filas en nuestra tabla
Ahora bien
Dividir Columna Por (Delimitador)
Podemos dividir una columna de texto que contiene dos o más categorías, de dos maneras diferentes: Por delimitador y Por Número de Caracteres.
En este caso utilizaremos la primera opción, pues si fijamos nuestra atención en la columna Cantidad – Categoría, claramente hay un carácter que define, donde inicia la siguiente categoría o columna, en este caso es un guion ( – )
Damos clic sobre la columna para elegirla –> Pestaña Transformar-> grupo Columna de Texto -> comando Dividir Columna -> opción Por Delimitador.
En el cuadro de dialogo Dividir Columna por delimitador, elegimos la opción Personalizado.
En el cuadro de texto que aparece, definimos el delimitado de la columna, en este caso un guion (-)
Después, habilitamos la opción En Cada aparición del delimitador y damos clic en Aceptar.
Automáticamente, se dividen las columnas:
Aunque podemos notar, que la segunda columna que se ha creado, no tiene los valores completos en sus celdas, pues aparece en blanco la categoría del producto.
Rellenar
Para que la columna muestre la categoría del producto y no celdas vacías debemos utilizar el comando Rellenar, pero antes debemos reemplazar los valores vacíos que aparecen en toda la columna.
Si damos clic sobre una celda, en el Panel de resultados, observamos que en la parte inferior del Editor de Consultas se muestra que contiene la celda:
En este caso, la celda tiene una cadena de caracteres, la palabra Alimentos, con letras en mayúsculas y minúsculas.
Ahora bien, si damos clic sobre la celda siguiente, observamos que no hay nada en esa celda; aunque hasta el momento Power Query no ha reconocido dichas celdas como valores nulos por lo que debemos hacer esta tarea.
Seleccionamos la Columna con los Valores Vacíos-> pestaña Inicio -> grupo Transformar -> Reemplazar los Valores
En la opción Valor que Buscar del cuadro de dialogo Reemplazar los valores, NO escribimos nada, mientras que, en Reemplazar Con escribimos la palabra null, así en minúscula:
Y clic en Aceptar
Si en este momento, damos clic en alguna celda que tenía valores vacíos, vemos que ahora Si lo reconoce como tal.
Finalmente, podemos utilizar el Comando Rellenar, que como su nombre lo dice, rellena con valores las celdas vecinas a una celda en una columna.
Seleccionamos la columna -> Pestaña Transformar -> grupo Cualquier Columna -> comando Rellenar -> opción Abajo
Poner en Mayúsculas cada Palabra
Este comando, transforma una cadena de texto, poniendo la letra mayúscula al inicio de la palabra y el resto en minúscula.
Clic derecho sobre la columna – > Transformar – > Poner en Mayúsculas Cada Palabra
Con ello, terminamos con esta columna
Por otra parte, si vamos a la columna Producto, hemos identificado que dos productos Café y Sal, tienen el nombre mal por lo que vamos a cambiarlo, Reemplazando los valores.
Clic derecho sobre la etiqueta de la columna -> Reemplazar los Valores ->
Y en el cuadro de dialogo escribimos, el valor a reemplazar y su sustituto.
Y repetimos el procedimiento con el otro producto.
Cambiar Nombre
Para cambiar el nombre de una columna
Doble clic izquierdo sobre la etiqueta -> Reescribimos el nombre de la misma
Luego, asignamos el Tipo de Dato de Cada Columna
Clic para Seleccionarla -> Pestaña Inicio -> grupo Transformar -> Comando Tipo de Datos -> Opción Número entero, en este caso
Y así repetimos el proceso para cada columna de la tabla
Con ello damos por terminada la etapa de Transformación, cabe resaltar que esto, es solo una pequeña muestra de lo que podemos lograr con Obtener y Transformar.
Cargar
Para Cargar la tabla construida por la Consulta, en la pestaña Inicio, vamos al grupo Cerrar y damos clic en Cerrar y Cargar en.
En el cuadro de dialogo que aparece configuramos las opciones de la siguiente manera:
Y clic en Cargar, automáticamente aparece una tabla estructura de Excel en una hoja nueva del libro, con los datos listos para ser procesados y utilizados:
Como lo has notado es recomendable utilizar Power Query cuando trabajamos en Excel, ya que puede ahorrarte tiempo en tareas de Extracción, Transformación y Carga de Datos
Hasta la Próxima