Mapa de Provincias de España en Excel

🔝Select the language of this blog post in the Google box at the top left.

Actualización de julio de 2022: Descarga una nueva versión mejorada de este mapa de provincias españolas con datos climáticos desde aquí:

Mapa del clima español | #ExcelPedroWave

Presentación del mapa

Continuando con la serie de artículos sobre mapas coropléticos en Excel, iniciado con el anterior artículo que puedes leer aquí:

Mapa Autonómico de España en Excel

Vamos a hacer un mapa de las provincias españolas en Excel para Microsoft 365 o Excel 2019 con datos de:

• Población del 1 de enero de 2021 según el INE, datos aquí.
• Superficie o área en km² según el INE, datos aquí.
• Densidad de población en habitantes/km².

En este mapa se aprecian las grandes diferencias en densidad de población entre las 2 ciudades más densamente pobladas (Madrid y Barcelona) y las 4 más vacías (Soria, Teruel, Huesca y Cuenca), pero de lo que se trata es de aprender a crear este mapa coloreado, o de descargar el mapa si no tienes intención de aprender...

Descarga del mapa

Descarga este gráfico de mapa coroplético con datos de población, superficie y densidad de las 50 Provincias y las 2 Ciudades Autónomas de España.

El mapa se puede descargar desde este enlace:

• Sites Google Drive: Mapa Provincias España PW7.xlsx

NOTAS:

• Recomiendo abrir el archivo descargado en Excel para Microsoft 365 o en Excel 2019, tanto para Windows como para Mac. Las versiones anteriores de Excel no soportan los mapas coropléticos.
• Habilita la edición al abrir el archivo.
• Si no dispones de versiones recientes de Excel, puedes abrir el archivo con Excel para iPad o iPhone o tabletas Android o teléfonos Android o Excel Mobile. En estas versiones no funciona la segmentación de datos, por lo que hay que filtrar directamente en la tabla.
• Si no vas a cambiar ningún dato geográfico puedes usarlo incluso sin conexión a Internet.
• Es ideal para aprender a geolocalizar las provincias en un mapa y a comparar los datos de las distintas provincias.
• Siguiendo este ejemplo puedes intentar hacer un mapa de las provincias de tu país y compartirlo con nosotros.

Vídeo del mapa

En este vídeo de 4 minutos explico cómo insertar un mapa de provincias en Excel y qué provincias españolas son detectadas incorrectamente en el mapa coroplético.

AVISO: En el vídeo introduje como nombre de provincia Las Palmas de Gran Canaria cuando en realidad es la provincia de Las Palmas, siendo su capital la ciudad de Las Palmas de Gran Canaria, en la isla de Gran Canaria. El nombre de esta provincia ya está corregido en el archivo descargable.

Sigue leyendo para saber cómo incluir las provincias que no detecta automáticamente el mapa coroplético o que geolocaliza incorrectamente.

Cómo detectar provincias en el mapa

Para que el mapa coroplético relacione los nombres de las provincias con su posición geográfica, lo mejor es añadir "Provincia de " a su nombre con una fórmula para la mayoría de las provincias, tal y como he explicado en el vídeo.

En el vídeo hay dos provincias que no aparecen en el mapa: Jaén y Vizcaya, pero algunas más son detectadas incorrectamente:

• Jaén: Detecta la provincia de Jaén del departamento de Cajamarca en el norte del Perú. Para que el mapa coroplético detecte la provincia de Jaén de la comunidad autónoma de Andalucía, al sur de la península ibérica, se debe escribir sin acento: Provincia de Jaen. El motivo habrá que preguntárselo a Microsoft...

• Vizcaya: Es una de las tres provincias españolas que componen la comunidad autónoma del País Vasco. Se debe escribir en euskera, como se denomina oficialmente: Bizkaia.

• Madrid, Navarra, Islas Baleares: He comprobado que son detectadas bien si no se buscan como "Provincia de ", sino únicamente por su nombre.

• Ceuta y Melilla: No son provincias sino ciudades autónomas, por lo que tampoco se deben llamar como provincias, sino únicamente por su nombre.

Datos de las provincias

La tabla con los datos de las provincias están en la hoja 'Provincias':

• Autonomías: Nombre de la Comunidad Autónoma (CCAA) o de la Ciudad Autónoma (CA).
• 50 Provincias + 2 CA*: Nombre de las provincias o de las ciudades autónomas. Ceuta* y Melilla* están marcadas con el símbolo asterisco (*) para diferenciarlas de las provincias.
• Provincias: Nombre de la provincia que reconoce el mapa coroplético. Comúnmente añadiendo "Provincia de " al nombre.
• Datos: Selecciona uno de los 3 tipos de datos con el desplegable de la celda Mapa!K2, con la fórmula: =INDICE($E2:$G2;1;COINCIDIR(Mapa!$K$2;$E$1:$G$1;0))
• Población (01/01/2021): Habitantes de cada provincia el 1 de enero de 2021 según el INE, datos aquí.
• Área (km²): Superficie de cada provincia según el INE, datos aquí.
• Densidad (hab/km²): Densidad de población de cada provincia el 1 de enero de 2021, como la división del número de habitantes por el área.

Ejemplo de las primeras filas de la tabla de provincias:

Mapa de las provincias

En la hoja 'Mapa' se ha insertado el mapa coroplético y dos segmentaciones de datos:

• Autonomías: para seleccionar las 17 autonomías o las 2 ciudades autónomas.
• 50 Provincias + 2 CA*: para seleccionar las 50 provincias o las 2 ciudades autónomas.

También se ha incluido el desplegable para elegir uno de los 3 tipos de datos en la celda K2:

• Población (01/01/2021)
• Área (km²)
• Densidad (hab/km²)

El origen de datos del mapa coroplético es: =Provincias!$C$1:$D$53

Con todas las provincias seleccionadas el aspecto del mapa es el siguiente:

Seleccionando una Autonomía se verán sus Provincias. En la siguiente imagen animada se ven las 17 Comunidades Autónomas españolas con su densidad de población por provincia:

Carencias del mapa

AVISO: No muestra nada al seleccionar las provincias de Murcia, La Rioja o León.

Es curioso que al seleccionar la comunidad autónoma de Castilla y León, si que detecta la provincia de León correctamente en el mapa:

SOLUCIÓN ALTERNATIVA: Para poder ver únicamente una de esas provincias, hay que seguir estos pasos.

Por ejemplo para ver solamente Murcia:

1) Filtrar por Murcia.

2) Manteniendo presionada la tecla Control, hacer clic con el ratón en Málaga. Al dejar de presionar la tecla Control se ven las dos provincias.

3) Volver a presionar la tecla Control, hacer clic con el ratón en Málaga. Al dejar de presionar la tecla Control se verá solamente Murcia.

Lo mismo pasa con La Rioja y León.

Esta solución alternativa no permite ver solamente las Islas Baleares, que siempre se ven junto con otras comunidades.

Estos problemas habrá que denunciarlos a Microsoft.

Si la conexión de Internet es de baja calidad, a veces se muestra el siguiente mensaje, que se corrige normalmente presionando el botón Actualizar:

Mis fuentes de inspiración han sido estas dos páginas:

• support.microsoft.com Crear un gráfico de mapa en Excel
• answers.microsoft.com Gráfico de Mapa con Provincias de España en Excel

En el próximo artículo publicaré un mapa coroplético de municipios españoles, mostrando como ejemplo los municipios de la provincia de Zaragoza.

Además comentaré algunas carencias al obtener datos de ubicación geográfica, de las que no he hablado en este artículo pues me ha sido imposible obtener el mapa de provincias partiendo de datos de información geográfica.

Enlaces a todos los artículos sobre los mapas coropléticos:

• Mapa Autonómico de España en Excel

• Mapa de Provincias de España en Excel

• Mapa de Municipios de Zaragoza en Excel

• Mapa del clima español

Mapa Autonómico de España en Excel

Introducción

En versiones de Microsoft® Excel® para Microsoft 365 o en Excel 2019 se pueden crear mapas coropléticos en los que las áreas se sombrean de distintos colores según determinados valores.

Como el curso escolar comienza en septiembre, estos mapas pueden facilitar la enseñanza de geografía en las escuelas, por su elevado grado de interacción del alumno con el mapa y por la posibilidad de añadir cualquier tipo de datos numéricos.

Se pueden crear mapas de cualquier país, región supranacional, estados o provincias de un país, ciudades o códigos postales... Y todo dentro de Excel sin usar macros VBA, ni complementos de mapas, ni Microsoft Power BI. Anímate a probarlo y verás lo didáctico e interactivo que es un mapa coroplético con escalas de colores.

Por ejemplo, la siguiente imagen animada muestra un mapa autonómico (¿o será mejor decir autónomo? ¡yo casi lo prefiero!) de las Comunidades Autónomas (CCAA) españolas de la Península Ibérica con datos de población (nº de habitantes según el censo):

Para insertar gráficos de mapas coropléticos en Excel, también denominados mapas rellenos (filled maps en inglés), abre el siguiente enlace:

Cómo crear un gráfico de mapa en Excel

En este artículo veremos como implementar un mapa con escala de colores por autonomías españolas, pudiendo elegir varios tipos de datos: población, área o superficie, densidad, hogares y turistas en 2020. Y lo mejor es que puedes añadir más tipos de datos o modificarlos a tu conveniencia. 

Vídeo del mapa

En este vídeo de 6 minutos explico cómo añadir datos de información geográfica de forma automática y cómo crear tu primer mapa de colores con las versiones más recientes de Excel.

Pero no te quedes sólo con el vídeo, sigue leyendo el artículo para saber mucho más sobre los mapas con información geográfica geolocalizada.

Datos del mapa

Para crear un mapa como éste lo primero que hay que hacer es diseñar una tabla con los datos geográficos en la hoja 'Datos'.

En la siguiente tabla se han insertado algunos datos de las Comunidades Autónomas (CCAA) y de las Ciudades Autónomas (CA) españolas:

La primera columna permite distinguir entre CCAA y CA. Hay un grupo especial para Cantabria y La Rioja y otro grupo para Canarias.

En la segunda columna se obtienen los nombres de las Comunidades y Ciudades, partiendo de sus abreviaturas ISO 3166-2:ES (lo que es muy recomendable para no tener discrepancias entre sí se trata de una comunidad o de una provincia), mediante el menú de la cinta de opciones: Datos > Información geográfica

Los datos a representar en el mapa son:

• Población: Obtenida como información geográfica con la fórmula: =[@[CCAA o CA]].Población

• Área (km²): Obtenida como información geográfica con la fórmula: =[@[CCAA o CA]].Área

• Habitantes/km2: Densidad de población como división de los dos datos anteriores.

• Hogares: Obtenidos como información geográfica con la fórmula: =[@[CCAA o CA]].Hogares

• Turistas 2020: Datos introducidos manualmente y obtenidos de la página: Movimientos turísticos en fronteras (FRONTUR) - DATAESTUR

• Imagen: Obtenida como información geográfica con la fórmula: =[@[CCAA o CA]].Imagen

• ISO 3166-2:ES: Abreviatura obtenida como información geográfica con la fórmula: =[@[CCAA o CA]].Abreviatura

• Capital/ciudad principal: Obtenida con la fórmula: =[@[CCAA o CA]].[Capital/ciudad principal]

• Ciudad más grande: Obtenida con la fórmula: =[@[CCAA o CA]].[Ciudad más grande]

• Datos CCAA o CA: Son los datos que se representarán en el mapa, obtenidos con la fórmula: =INDICE($D2:$H2;1;COINCIDIR(Mapa!$N$1;$D$1:$H$1;0))

Haciendo clic en el símbolo del mapa se puede mostrar la tarjeta con los datos geográficos obtenidos automáticamente por Excel. También se puede mostrar la tarjeta (como si fueras un árbitro de fútbol) con la combinación de estas 3 teclas: Ctrl+Mayús+F5

Crear un mapa coroplético

Para crear el mapa se seleccionan los datos en el rango B1:C20 y se selecciona desde la cinta de opciones: Insertar > Mapas > Mapa coroplético, como se ve en la siguiente imagen.

Este mapa ha sido movido a la hoja 'Mapa' en la que se han insertado un par de segmentaciones de datos para poder filtrarlo más cómodamente, con lo que se puede visualizar una sola comunidad o ciudad autónoma o un grupo de ellas:

• Ciudad Autónoma: Son Ceuta y Melilla.
• Comunidad Autónoma: Todas excepto Canarias, Cantabria y La Rioja.
• Comunidad Autónoma Canarias: Para visualizar sólo Canarias. Si no se selecciona Canarias se ven sólo las autonomías de la Península Ibérica.
• Comunidad Autónoma Cantabria y La Rioja: Se han agrupado a parte para intentar evitar los problemas que generan estas dos comunidades con las etiquetas de datos, como se explicará más adelante...

En la celda N1 se ha incluido una lista desplegable para poder cambiar los tipos de datos en el mapa:

Este mapa es totalmente interactivo y no necesita una conexión de datos para filtrar una Comunidad Autónoma o una Ciudad Autónoma o un grupo de ellas, y se puede representar cualquier dato, no sólo los datos geográficos obtenidos automáticamente de Wikipedia.

Pasando el ratón por encima de cada región autonómica se muestran sus datos:

Para no obligar a pasar el ratón por encima de cada autonomía para ver su Serie y Valor, he incluido las Etiquetas de datos para cada autonomía, como se ven en la figura anterior.

Carencias de los datos geográficos

Enumeraré los principales problemas que me he encontrado para diseñar este mapa, comenzando por los datos geográficos de la hoja 'Datos':

• Los datos de Población son de distintos años. Por ejemplo, para Aragón son de 2018 y para Cataluña son de 2020, por lo que no son uniformes... Para colmo, se enlaza a esta página con datos del 1 de enero de 2021: Población residente por fecha, sexo y edad(9681) (ine.es)

• Los datos de Hogares son de 2011 cuando el INE dispone de datos de 2020. Ver enlace: INEbase / Demografía y población /Cifras de población y Censos demográficos /Encuesta continua de hogares / Últimos datos

• En la columna J con las abreviaturas ISO 3166-2:ES confunde la Comunidad Autónoma con la provincia para estas CCAA: Asturias (ES-O en lugar de ES-AS); Castilla-La Mancha (No disponible); Comunidad de Madrid (ES-M en lugar de ES-MD); Navarra (ES-NC en lugar de ES-NA) y La Rioja (No disponible). De lo que se deduce que interpreta erróneamente algunas regiones autónomas como provincias y otras ni aparecen. Es mas seguro obtener las abreviaturas desde este enlace: GeoNames

• No aparece la imagen de la bandera o el escudo de Melilla sino un mapa nada representativo.

• Las superficies del campo Área (km²) no coinciden para alguna CCAA, como Andalucía para la que se obtiene una superficie de 87.268 km² cuando el INE da un valor de 87.599 km² en la página: pdfDispacher.do (ine.es)

• Capital/ciudad principal: Para Galicia no encuentra que su capital es Santiago de Compostela. Para Canarias da como capital a Las Palmas de Gran Canaria cuando está compartida la capital entre Las Palmas de Gran Canaria y Santa Cruz de Tenerife.

• Ciudad más grande: Debería decir Ciudad más poblada. Hay varias incorrecciones como que en Galicia la ciudad más grande es ¿la Provincia de La Coruña? en lugar de Vigo, o que en Navarra es la Cuenca de Pamplona, o que en Extremadura es la Provincia de Cáceres.

Vistas las carencias en los datos de información geográfica, suministrados automáticamente en Excel 2019 y en Excel para Microsoft 365, lo mejor es no usarlos y crear nuestros datos desde cero, partiendo de fuentes de datos fiables, como los últimos datos del INE.

Visto lo visto, pienso escribir otro artículo sobre un mapa de provincias españolas y sus carencias, que puede ser más sorprendente aún que éste... ¡Anotado en la agenda!

¡Hecho! Enlace aquí:

Mapa de Provincias de España en Excel

Carencias de los mapas coropléticos

Estos mapas serían mejores si Microsoft implementara correctamente las etiquetas de datos geográficos, como hace cualquier aplicación que incluye mapas, como Google Maps, TomTom o GeoNames (enlace aquí).

En estas dos últimas aplicaciones y en Wikipedia se basan los datos de información geográfica y los gráficos de los mapas coropléticos que emplea Microsoft en Excel, por lo que no sería mucho pedir que esos datos se mostraran adecuadamente sobre el mapa. Pero no es así, como vamos a ver a continuación:

• Etiquetas de mapa: No confundir con las Etiquetas de datos. Haciendo clic con el botón derecho del ratón sobre el mapa y seleccionando Dar formato a serie de datos... se pueden cambiar las Opciones de serie, donde se decide cómo mostrar las Etiquetas de mapa, cosa que es mejor no usar por varias razones:
• Ninguna - Es la opción mejor pues las otras 2 opciones no aseguran que se muestren correctamente las etiquetas.
• Solo mejor ajustadas - Con esta opción únicamente aparecerán algunas etiquetas.
• Mostrar todo - ¡No es verdad que se muestren todas las etiquetas como se ve en la siguiente imagen! Por ejemplo, la Comunidad de Madrid no tiene etiqueta y la Comunidad Valenciana aparece como Co... Val...

• Lo único a favor de las etiquetas de mapas es que aparece el nombre de la Comunidad Autónoma mejor que en la información geográfica. Parece ser que el nombre corto lo busca en Wikipedia. Por ejemplo, Asturias se muestra como Principado de Asturias o Navarra como Comunidad Foral de Navarra. En algunos casos también aparece el nombre en su lengua autóctona: Galicia / Galiza; País Vasco / Euskadi; Comunidad Valenciana / Comunitat Valenciana.

• Cuando Microsoft corrija en alguna versión de Excel el ajuste automático de las Etiquetas de mapa, para poder mostrar todas las etiquetas aunque se superpongan sobre distintas regiones, se podrá hacer uso de las mismas, mientras tanto no recomiendo usarlas...

• Etiquetas de datos: Son el tipo de etiqueta que muestro en el mapa que se puede descargar más abajo. Después de agregar las etiquetas de datos se marcan como Opciones de etiqueta el Nombre de la categoría y su Valor, como Separador: Nueva línea, y como Relleno: Relleno degradado.

• Tamaño: 4 para que quepan todas las etiquetas en sus correspondientes regiones. Ya sé que es un valor muy pequeño pero lo hago para no tener que definir un tamaño distinto para cada autonomía, y que se puedan leer tanto la categoría como el valor dentro de la etiqueta.

• ADVERTENCIA: Es impensable que cuando se seleccionan Cantabria y/o La Rioja desaparecen el resto de etiquetas, ¡pero es lo que pasa!. Habrá que avisar a Microsoft de este problema, error, fallo, bug, que da muy mala imagen en un mapa coroplético, en el que no hay forma de mostrar todas las etiquetas de datos. La única solución alternativa, método temporal, paliativo,  workaround, es filtrar estas dos autonomías invisibilizándolas, para que aparezcan las etiquetas de las demás autonomías. Por eso ha hecho falta añadir la segmentación de datos con las CCAA de Cantabria y La Rioja separadas del resto.

Descarga del mapa coroplético

Descarga este gráfico de mapa autónomo, hecho en Microsoft 365 para Excel, con las 17 Comunidades Autónomas y las 2 Ciudades Autónomas de España.

Si no vas a cambiar ningún dato geográfico puedes usarlo incluso sin conexión a Internet. Es ideal para aprender a geolocalizar autonomías en un mapa y a comparar los datos de las distintas autonomías.

El mapa se puede descargar desde este enlace:

• Sites Google Drive: Mapa España PW4.xlsx

Derecho de autor

Yo, Pedro Wave, estoy publicando bajo una Licencia Creative Commons: 

Atribución-NoComercial-CompartIgual 3.0 No portada (CC BY-NC-SA 3.0)

https://creativecommons.org/licenses/by-nc-sa/3.0/

Los términos de la licencia son:

• Atribución: Otorgue el crédito apropiado, manteniendo mi nombre y el nombre de mi blog en el libro de trabajo y el código.
• Compartir igual: Si remezcla, transforma o crea a partir del material, debe distribuir su contribución bajo la misma licencia del original.
• No comercial: Usted no puede hacer uso del material con propósitos comerciales.

Resumen

🌍 Estos mapas son muy fáciles de hacer en Excel pero hay que tener cuidado con sus carencias y sus limitaciones.

🗺 Sugiero poner siempre en duda la información geográfica que se obtiene automáticamente, para lo que hay que asegurar que los datos geográficos son los esperados.

⁉ Como buenos analistas de datos 📊 debemos acostumbrarnos a comparar los datos geográficos geolocalizados con la información de fuentes fidedignas, antes de publicarlos o usarlos como una estadística fiable o en la enseñanza 👨‍🏫 👩‍🎓 en las aulas.

⛵ Así llegaremos a buen puerto, usando buenos mapas y cartas de navegación...

Enlaces a todos los artículos sobre los mapas coropléticos:

• Mapa Autonómico de España en Excel

• Mapa de Provincias de España en Excel

• Mapa de Municipios de Zaragoza en Excel

Catch the Ball Game - Juego Atrapa la Bola

Juego Atrapa la Bola

Este juego está totalmente diseñado en Excel con macros VBA y tendrás un minuto para atrapar todas las bolas que puedas mientras van rebotando en un rango rectangular de celdas.

Este minijuego del verano se trata de hacer clic encima de la bola mientras se mueve por una hoja Excel. Las bolas están numeradas y al atrapar una bola aumenta el contador de bolas atrapadas y aparece una nueva bola desde abajo, en cualquier ángulo, y comienza a moverse. Los primeros 30 segundos su velocidad es constante y los últimos 30 segundos va acelerando progresivamente.
Esta es la pantalla del juego:

Catch the Ball Game

This game is fully designed in Excel with VBA macros and you will have one minute to catch as many balls as you can while bouncing on a rectangular cell range.

This summer minigame is all about clicking on the ball while moves through an Excel sheet. The balls are numbered and when catching one ball increases the caught ball counter and a new ball appears from down, at any angle, and begins to move. The first 30 seconds its speed is constant and the last 30 seconds it accelerates progressively.

This is the game screen:

Descargar el juego

Para poder jugar hay que permitir la edición y habilitar las macros.

El juego se puede descargar desde estos dos enlaces:

Download the game

In order to play, you must allow editing and enable macros.

The game can be downloaded from these two links:

Microsoft OneDrive: Atrapa la bola - Catch the ball - PW1.xlsm

Sites Google Drive: Atrapa la bola - Catch the ball - PW1.xlsm

Derecho de autor

Yo, Pedro Wave, estoy publicando bajo una Licencia Creative Commons: 

Atribución-NoComercial-CompartIgual 3.0 No portada (CC BY-NC-SA 3.0) https://creativecommons.org/licenses/by-nc-sa/3.0/

Los términos de la licencia son:

• Atribución: Otorgue el crédito apropiado, manteniendo mi nombre y el nombre de mi blog en el libro de trabajo y el código.
• Compartir igual: Si remezcla, transforma o crea a partir del material, debe distribuir su contribución bajo la misma licencia del original.
• No comercial: Usted no puede hacer uso del material con propósitos comerciales.

Copyright

I, Pedro Wave, am publishing under a Creative Commons License: 

Attribution-NonCommercial-ShareAlike 3.0 Unported (CC BY-NC-SA 3.0)

https://creativecommons.org/licenses/by-nc-sa/3.0/

Under the following terms:

• Attribution: You must give appropriate credit to my blog, provide a link to the license, and indicate if changes were made.
• ShareAlike: If you remix, transform, or build upon the material, you must distribute your contributions under the same license as the original.
• NonCommercial: You may not use the material for commercial purposes.

Vídeo del juego

Game video

Instrucciones del juego

Si este juego necesita instrucciones, ¡apaga y vámonos!

La única instrucción que vale es abrir el editor de VBA y estudiar el código de las macros para aprender a hacer un juego como éste en Excel.

Cambia la forma de la bola haciendo clic en el título del juego.

Cambia de jugador haciendo clic en uno de los diez mejores jugadores.

Cambia el sonido del juego entre: ON - OFF - ONE (sólo suena cuando atrapas la bola).

Cambia la velocidad de la bola: 1 a 10.

¡Que pases un buen verano!

Game instructions

If this game needs instructions, let's get out of here!

The only instruction that works is to open the VBA editor and study the macro code to learn how to make a game like this in Excel.

Change the shape of the ball by clicking on the game title.

Change the player's name by clicking on one of the top ten players.

Switch the game sound between: ON - OFF - ONE (only sounds when you catch the ball).

Change the ball speed from 1 to 10.

Have a great summer!

Power Query - multiple slicers (4/4)

This is the fourth solution with Power Query to transform a poorly designed table into a pivot table but with multiple repeating rows.

In the following dynamic image you can see the 9 steps applied to transform the table.

Fourth solution

This fourth and last solution doesn't use a generic function, as in the previous solutions, it only uses these 9 steps applied with Power Query, which can be seen in the Advanced Editor:

let Source = Excel.CurrentWorkbook(){[Name="TableDetails"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Type", "Details"}, "Name", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Value"}), #"Group Clust Index" = Table.Group( #"Removed Columns",List.RemoveItems(Table.ColumnNames(#"Removed Columns"),{"Details"}) ,{"ColOfTables",each Table.AddIndexColumn(_,"idx")}), #"Expanded ColOfTables" = Table.ExpandTableColumn(#"Group Clust Index", "ColOfTables", {"Details", "idx"}, {"Details", "idx"}), #"Sorted Rows" = Table.Sort(#"Expanded ColOfTables",{{"idx", Order.Ascending}}), #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Type]), "Type", "Details"), #"Removed Index" = Table.RemoveColumns(#"Pivoted Column",{"idx"}), #"Filled Down" = Table.FillDown(#"Removed Index",{"Locations", "Ages", "Skills"}) in #"Filled Down"

Below I explain each of these applied steps:

1) Source = Excel.CurrentWorkbook(){[Name="TableDetails"]}[Content]

TableDetails is the original table with the Details column, with the details of the 3 data types listed in the Type column: Locations, Ages & Skills. The rest of columns are names of people with a Y o y to indicate that they are part of a specific detail of one of the data types. For example, Mary has been to two different locations: London & Joburg, which should be considered when pivot the table in the step 7).

2) #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Type", "Details"}, "Name", "Value"),

In this step, the 2 columns on the left (Type & Details) are selected to unpivot the columns with Names, creating a normalized table with 2 additional columns: Name & Value, inserting a row for each pair of values of those columns. Null values don't create new rows. The Table.UnpivotColumns function forces the selected columns to be unpivoted, so the Table.UnpivotOtherColumns function is used, which is independent of the number of columns to be unpivoted, so it is not necessary to select each column with a Name who, a priori, it is not known.

3) #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Value"}),

The Value column is not relevant so it is removed. Notice that it contains both the uppercase Y letter and the lowercase y letter, to indicate that a type detail exists for a given name.

4) #"Group Clust Index" = Table.Group( #"Removed Columns",

List.RemoveItems(Table.ColumnNames(#"Removed Columns"),{"Details"}),

{"ColOfTables",each Table.AddIndexColumn(_,"idx")}),

This step is very important because it creates a column composed of "ColOfTables" tables, one for each pair of Type & Name values, with as many rows as there are Details records for each pair of Type & Name values, adding a consecutive index column " idx" to distinguish each row in that table. The 18 rows from above step 3) have been converted to 15 rows in which the Type & Name value pairs are not duplicated.

5) #"Expanded ColOfTables" = Table.ExpandTableColumn(#"Group Clust Index", "ColOfTables",        {"Details", "idx"}, {"Details", "idx"}),

This step expands the values of the tables of the column "ColdOfTables" in two columns: Details & idx (index of each table), so that the 15 rows become 18 rows in this case, being 3 details with the same pair of Type & Name values, and their indices are 0 and 1 respectively.

6) #"Sorted Rows" = Table.Sort(#"Expanded ColOfTables",{{"idx", Order.Ascending}}),

The trick to correctly pivot this table in the next step 7) is that the index column "idx" is in ascending order.

7) #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Type]), "Type",        "Details"),

This step pivots the table by creating 3 Type columns: Ages, Skills & Locations, with their Details values for each Name. It is observed that it is not an ordinary pivot table, as there are duplications in the Name column, discriminated by the index "idx" column.

8) #"Removed Index" = Table.RemoveColumns(#"Pivoted Column",{"idx"}),

Right now you can remove the index "idx" column, as it has already played its role pivoting the table with repeated names into the several rows.

9) #"Filled Down" = Table.FillDown(#"Removed Index",{"Locations", "Ages", "Skills"})

This last step fills in the nulls with the values on the above row that correspond to the same Name.

This fourth solution allows you to see the result of each step applied in the Power Query Editor, which is not elementary with a generic function, since it applies all the steps without being able to see what each step does with the tables tranformation. This solution also makes it easier to copy these steps in M language code from the Power Query Advanced Editor to paste it into another Excel workbook or even into Power BI.

These 9 steps are a good example that Power Query is an excellent ETL tool to Extract, Transform & Load. An additional loading step is done just before closing the Power Query Editor, choosing to load the transformed table as a normal Excel table.

It is possible to insert in this table 3 slicers, one for Locations, another for Ages and another for Skills, as it was intended to achieve in the statement of the problem that was raised in this forum:

MrExcel.com - multiple slicers

I have a table of data with locations, ages, and skills in the left hand column. Can a slicer be set up for each data type? ie one for the locations, one for the ages, and one for the skills.

Fourth solution download

• From this link to Microsoft OneDrive:

Multiple Slicers PW4.xlsx

• From this link to Sites Google Drive:

Multiple Slicers PW4.xlsx

This post completes the 4 solutions proposed to solve this problem, which I hope will help my readers to start and experiment with Power Query, with all its potential as an ETL tool, which allows solving complex problems without the need to program avanced Excel formulas and/or macros VBA.

Power Query - multiple slicers (3/4)

In the previous post we saw a generic function that allows you to pivot a table in multiple rows:

Power Query - multiple slicers (2/4)

In this post we will see a second version of that generic function, as published by Cameron Wallace on GitHub:

camwally / Power-Query / fNonAggPivotMultRows2.pq

Third solution

This solution has its origin in the normalized detail table and, applying the generic function, it becomes a pivoted table with multiple rows if there is more than one location, age or skill per person.

This case cannot be solved with dynamic tables, since they do not admit several rows with the same Name.

The M code in Power Query for the generic function that pivot the table is as follows:

Source as table ,PivotCol as text ,ValueCol as text)=> let Source = Table.Buffer(Source) //As source table is referenced 3 times ,GroupClustIndex = Table.Group(Source ,List.RemoveItems(Table.ColumnNames(Source),{ValueCol}) ,{"ColOfTables" ,each Table.AddIndexColumn(_,"idx")}) ,CombineTables = Table.Combine(GroupClustIndex[ColOfTables]) ,Pivot = Table.Pivot(CombineTables, List.Distinct(Table.Column(Source,PivotCol)), PivotCol, ValueCol) ,RemoveIndex = Table.RemoveColumns(Pivot,{"idx"}) in RemoveIndex

The way to call this function is:

= #"Pivot Duplicates Function"(TableDetails, "Type", "Details")

3 arguments are passed: Source as table, PivotCol as text and ValueCol as text.

The 5 steps applied by the generic function are explained below:

1) Source = Table.Buffer(Source)

//As source table is referenced 3 times, buffers the Source table in memory, isolating it from external changes during evaluation.

2) GroupClustIndex = Table.Group(Source,

List.RemoveItems(Table.ColumnNames(Source),{ValueCol}),

{"ColOfTables",each Table.AddIndexColumn(_,"idx")})

//Groups rows in the table that have the same key with List.RemoveItems function, adding an index column called "ColOfTables".

3) CombineTables = Table.Combine(GroupClustIndex[ColOfTables])

//Combine main table with the tables in the "ColOfTables" column.

4) Pivot = Table.Pivot(CombineTables,

List.Distinct(Table.Column(Source,PivotCol)), PivotCol, ValueCol)

//Pivot Source tabla with the PivotCol adding one column for each "Type" with the ValueCol from "Details" values. 

5) RemoveIndex = Table.RemoveColumns(Pivot,{"idx"})

//Remove auxiliar index.

 

Third solution download

• From this link to Microsoft OneDrive:

Multiple Slicers PW3.xlsx

• From this link to Sites Google Drive:

Multiple Slicers PW3.xlsx

With the generic function, you cannot click individual steps and see how the query transforms the data, so a fourth solution is required.

You can read the following post talking about the last fourth solution, without the generic function and with only a few steps applied in Power Query:

Power Query - multiple slicers (4/4)

Power Query - multiple slicers (2/4)

In the second solution to the problem of being able to insert multiple slicers in a table, we just use Power Query to solve it, being the first query equal to that of the first proposed solution: unpivoting other columns.

You can read how to unpivot the original table in this link:

Power Query Table to set up multiple slicers

Second solution

The second query with Power Query is based on the following article:

Dingbat Data - Non-aggregate pivot with multiple rows in Power Query

Cameron Wallace published in that post a generic function that solves the case there are multiple detail values for the same data type and the same person.

The generic function in Power Query looks like this:

As source, we start from the unpivoted table (TableDetails) and pass two arguments: the column to pivot (Type) and the column with the values (Details).

To understand it, TableDetails is a normalized table with 3 columns that cannot be pivoted in the usual way, as it contains multiple values of detail for the same name and type, so it will be necessary to create additional rows when this table is pivoted:

For example, Mary has lived in two different locations: London and Joburg, so it takes 2 rows to pivot that data.

For this, the Pivot Duplicates Function has been defined with this Power Query M code:

(Source as table ,ColToPivot as text ,ColForValues as text)=> let PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))) ,#"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _) ,TableFromRecordOfLists = (rec as record, fieldnames as list) => let PartialRecord = Record.SelectFields(rec,fieldnames) ,RecordToList = Record.ToList(PartialRecord) ,Table = Table.FromColumns(RecordToList,fieldnames) in Table ,#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)) ,#"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames) ,#"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames) in #"Expanded Values"

I am not going to explain these M formulas in detail because they are not mine and are explained in the blog where this function was originally published (link here). In a next post I'll publish the fourth proposed solution, where I'll explain some of these formulas.

The way to call this function is:

= #"Pivot Duplicates Function"(TableDetails, "Type", "Details")

With which the TableDetails with its Details is pivoted by the Type, resulting in the following table:

For each Name of a person, one or more rows are obtained with all their Locations, Ages & Skills.

Null values indicate that this value is the one in the previous row, so the next step is to fill those values down with this function:

= Table.FillDown(Source,{"Locations", "Ages", "Skills"})

With which the desired second solution is achieved:

Now multiple slicers can be inserted into this table, as requested in the initial forum query.

Second solution download

• From this link to Microsoft OneDrive:

Multiple Slicers PW2.xlsx

• From this link to Sites Google Drive:

Multiple Slicers PW2.xlsx

I've posted the third solution with a simplified version of the generic function that has been used in this second solution. Access it at the following link:

Power Query - multiple slicers (3/4)

Power Query Table to set up multiple slicers

A week ago I answered a question from the forum MrExcel.com asking for help creating multiple slicers for a table like this:

MrExcel.com - multiple slicers

I have a table of data with locations, ages, and skills in the left hand column. Can a slicer be set up for each data type? ie one for the locations, one for the ages, and one for the skills.

The problem is that locations, ages and skills are in the same column, so slicers cannot be created for those data types with a table that is not normalized.

If the table were normalized it would be very easy to insert slicers. For example in this table:

When the data layout is all in column A, as in the first table, we need to transform that table to get a table with a column for each type of data: locations, ages and skills.

Transformations

First of all, it is necessary to indicate what type of data is in each row of the original table, including a new column on the left for the data types, as in the following table:

With this new column, it is perfectly determined what type of data each detail corresponds to, something that humans find easy to associate because we have natural intelligence, but that machines and spreadsheets find it impossible without artificial intelligence, and we have to give them concrete ideas, so they can associate each field with data to its specific entity.

Below I explain my human logic used to try to solve this problem. In this post I am going to propose 4 possible solutions to this problem, following the flow of my reasoning, as I try more formulas in Power Query M language.

All the 4 proposed solutions go through unpivot columns thanks to the Power Query tool (link here).

First solution

I use Power Query to select the Type and Details columns and unpivot other columns with each person's data. Also I insert a new merged column: Name-Type. This is the result:

With the previous table as a data source, I have inserted a dynamic table (left) and an auxiliary table with formulas (right), which will need to be adjusted in size each time the source data is updated:

One more pivot table must be created with data source in the auxiliary table and finally the slicers are created:

First solution download

• From this link to Microsoft OneDrive:

Multiple Slicers PW1.xlsx

• From this link to Sites Google Drive:

Multiple Slicers PW1.xlsx

In this file you can analyze:

• Power Query M code

let Source = Excel.CurrentWorkbook(){[Name="TableDetails"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Details", type text}, {"Mike", type text}, {"Denis", type text}, {"Mary", type text}, {"Sally", type text}, {"Peter", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Details", "Type"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Name"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Value"}), #"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Name-Type", each Text.Combine({[Name], [Type]}, "-"), type text) in #"Inserted Merged Column"

The main M function is Table.UnpivotOtherColumns, which allows unpivot other non-selected columns, in this case the people names as "Attribute":

Table.UnpivotOtherColumns(#"Changed Type", {"Details", "Type"}, "Attribute", "Value")

Univot data is more complicated with VBA than with Power Query. See VBA code to unpivot data here.

• Auxiliary table formulas, as in F2 cell:

=IFERROR(VLOOKUP($E2 & "-" & F$1,INDIRECT("$B" & ROW($E2) & ":$C$" & COUNTA($A:$A)),2,FALSE),F1)

• Pivot tables with slicers as the above image.

You can read the second solution to this problem here:

Power Query - multiple slicers (2/4)