lunes, 25 de marzo de 2019

Power Query: Agrupar y Extraer valores

Volvemos con la herramienta de moda por su alto rendimiento: Power Query.
Tiempo atrás una lectora consultaba por la maneara de listar de una manera concreta datos a partir de un listado.
La idea es la que se ve en la siguiente imagen:

Power Query: Agrupar y Extraer valores



El objetivo es claro, pasar de un listado vertical de países y ciudades a un listado de filas únicas por país, mostrando en su misma fila, las diferentes ciudades de éste.

Como siempre comenzaremos cargando a Power Query (Obtener y Transformar) la tabla principal 'TblPaises')

Power Query: Agrupar y Extraer valores



El siguiente paso a incorporar a nuestra consulta es fundamental. Procederemos a Agrupar por... en su modo avanzado:

Power Query: Agrupar y Extraer valores


la agrupación se ha basado en los elementos del campo 'País', acumulando 'Todos las filas' del campo de la 'Ciudad'.
Y acabamos acumulando, por Suma, el número de habitantes de cada Ciudad...


Esto deja nuestra consulta como sigue:

Power Query: Agrupar y Extraer valores


Comprueba como el campo nuevo (que hemos llamado 'ciudades' en la anterior agrupación) muestra a modo de tabla el resultado...
Esto lo vamos a personalizar, y en la barra de fórmula (por ejemplo... también en el editor avanzado) cambiaremos:
each _, type table
por
each [Ciudad]
como sigue...
= Table.Group(#"Changed Type", {"País"}, {{"ciudades", each _, type table}, 
   {"Hab total", each List.Sum([Habitantes]), type number}})
por
= Table.Group(#"Changed Type", {"País"}, {{"ciudades", each [Ciudad]}, 
   {"Hab total", each List.Sum([Habitantes]), type number}})

Este cambio provoca que el campo nuevo 'ciudades' se muestre como Lista y no como Tabla!!

Power Query: Agrupar y Extraer valores



El último paso es fácil... basta hacer clic sobre el botón de expandir sobre ese campo 'ciudades' y elegir la segunda opción: Extraer valores...

Power Query: Agrupar y Extraer valores


Configuramos en qué forma queremos extraer los valores eligiendo el separador en ventana siguiente y aceptamos

Power Query: Agrupar y Extraer valores



Y una vez finalizado el proceso ya podemos Cargar y cerrar en... nuestra hoja de cálculo con el resultado del inicio del post, tal como queríamos conseguir.

4 comentarios:

  1. Seria genial tener los archivos de excel de ejemplo.

    ResponderEliminar
    Respuestas
    1. Hola Osiel
      solo sigue los pasos descritos... y cualquier duda en el proceso comenta
      Saludos

      Eliminar
  2. Hola Ismael,

    Por si no lo sabías, la función Table.Group() tiene un modificador opcional (groupKind) con dos posibles valores: "Group.KindGlobal" (por defecto) y "Group.KindLocal". Este último hace que se agrupen solamente las filas consecutivas, y además es bastante más rápido (en tablas grandes).

    Puede parecer una melonada pero no lo es. Te pongo un ejemplo tonto con una tabla con dos columnas:

    C1 C2
    A 1
    B 2
    A 3
    A 4
    B 5

    Si agrupo la columna "C1" sumando los valores de "C2" con el modificador por defecto "Group.KindGlobal", me devolverá una tabla con dos filas:

    C1 C2
    A 8
    B 7

    Si agrupo la columna "C1" sumando los valores de "C2" pero con el modificador "Group.KindLocal", me devolverá esta tabla (espero):

    C1 C2
    A 1
    B 2
    A 7
    B 5

    Esta semana me acordé de esto agrupando fechas y me vino de perlas.

    ResponderEliminar
    Respuestas
    1. Gracias Daniel
      como siempre muy interesante el aporte
      ;-)
      Un saludo

      Eliminar

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