#ExcelPedroWave

En la segunda parte de este tutorial vimos cómo transformar datos dinamizados en normalizados con Power Query:

Tutorial 2 Power Query - Carrera de Barras

Ahora veremos cómo transformar los datos acumulados en datos diarios gracias a un buen truco con Power Query para poder calcular diferencias de un dato con el previo.

Truco para transformar datos acumulados en diarios

Ismael Romero es el autor del blog ExcelFORO, de obligada lectura para quienes queremos aprender a sacarle el máximo provecho a Excel. En el siguiente artículo publicó el truco que me ha ayudado a resolver la transformación de datos acumulados en datos diarios:

Power Query: Diferencia con el dato anterior

En este artículo se explica cómo calcular diferencias de un dato con el previo, bien en valor absoluto o bien la variación porcentual, aunque el valor porcentual no hace falta para transformar la carrera de barras.

Cómo transformar la carrera en datos diarios

Al extraer los datos originales de la Web se obtienen datos acumulados para la carrera de barras de los 10 países con más fallecimientos oficiales por Covid-19. Para correr la carrera con datos diarios hay que transformar los datos acumulados por país, calculando la diferencia con el dato acumulado anterior, para lo que he empleado el truco de Ismael Romero.

Para transformar los fallecimientos acumulados cada día en cada país en fallecimientos diarios por país, los pasos aplicados son:

1. Índice agregado: Se agrega columna de índice desde 0
2. Índice agregado1: Se agrega columna de índice desde 1
3. Consultas combinadas: Combina índices y crea tablas indexadas
4. Se expandió Índice agregado1: Se expande el valor de la tabla indexada
5. Filas ordenadas: Se ordena por el índice desde 0
6. Personalizada agregada: Diferencia entre el valor y el valor anterior
7. Columna condicional agregada: Cuando las filas son de distinto país, si la diferencia <0 entonces 0
8. Columnas quitadas: Quita todas las columnas auxiliares
9. Tipo cambiado: Cambia el tipo de valor a número
10. Errores reemplazados: Reemplaza error por 0 en la fila 1

En esta imagen se pueden ver los 10 pasos aplicados:

Los pasos aplicados se guardan automáticamente en Lenguaje M, mientras se van generando con los menús asistidos de Power Query, y se pueden ver con el Editor avanzado de Power Query:

// Se agrega columna de indice desde 0 #"Índice agregado" = Table.AddIndexColumn(#"Columnas con nombre cambiado", "Índice", 0, 1, Int64.Type), // Se agrega columna de indice desde 1 #"Índice agregado1" = Table.AddIndexColumn(#"Índice agregado", "Índice.1", 1, 1, Int64.Type), // Combina indices y crea tabla de la izquierda #"Consultas combinadas" = Table.NestedJoin(#"Índice agregado1", {"Índice"}, #"Índice agregado1", {"Índice.1"}, "Índice agregado1", JoinKind.LeftOuter), // Se expande el valor de la tabla indexada #"Se expandió Índice agregado1" = Table.ExpandTableColumn(#"Consultas combinadas", "Índice agregado1", {"Value"}, {"Índice agregado1.Value"}), // Se ordena por el índice desde 0 #"Filas ordenadas" = Table.Sort(#"Se expandió Índice agregado1",{{"Índice", Order.Ascending}}), // Diferencia entre el valor y el valor anterior #"Personalizada agregada" = Table.AddColumn(#"Filas ordenadas", "Difference", each [Value]-[Índice agregado1.Value]), // Cuando las filas son de distinto país, si la diferencia <0 entonces 0 #"Columna condicional agregada" = Table.AddColumn(#"Personalizada agregada", "Increment", each if [Difference] < 0 then 0 else [Difference]), // Quita todas las columnas auxiliares #"Columnas quitadas" = Table.RemoveColumns(#"Columna condicional agregada",{"Índice", "Índice.1", "Índice agregado1.Value", "Difference"}), // Cambia el tipo de valor a número #"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas quitadas",{{"Increment", Int64.Type}}), // Reemplaza error por 0 en la fila 1 #"Errores reemplazados" = Table.ReplaceErrorValues(#"Tipo cambiado", {{"Increment", 0}}) in #"Errores reemplazados"

¡Anda que no hay que dar pasos para restar el valor de una fila del valor de la fila anterior!

¡Con fórmulas en Excel bastaría el paso 6!

La principal ventaja de hacer la transformación con Power Query es que sólo se hace la transformación cuando se cargan más datos de la Web, como mucho una vez al día. Con datos de 2 años harían falta actualizar los datos de 730 días para más de 200 países, por lo que se recalcularían unas 146.000 filas cada día que cambiara la animación de la carrera de barras, lo que ralentizaría tremendamente la carrera.

Con Power Query, la carga de datos acumulados y datos diarios se hace solamente cuando se consulta la Web, por lo que la tabla cargada es estática y es el origen de datos de las tablas dinámicas, que veremos en un próximo tutorial dentro de unos días. Como no hace falta refrescar las tablas dinámicas si no se consultan nuevos datos de la Web, podemos considerar que las tablas dinámicas son "estáticas" para nuestra carrera. Por lo tanto únicamente hay que preparar los datos a visualizar durante la carrera, consiguiendo que la carrera sea los más animada posible.

¡Vaya paradoja! ¡Usar tablas dinámicas como tablas estáticas!

Pasos aplicados para transformar la carrera

En el artículo anterior se explicó cómo obtener una tabla normalizada con datos acumulados en la columna: Value

En este artículo se explica cómo obtener datos diarios en la columna: Increment, aplicando 10 nuevos pasos, como se puede ver en esta imagen:

Para continuar donde lo dejamos en el artículo anterior, se abre el Editor de Power Query desde el menú de Excel: Datos > Obtener datos > Iniciar Editor de Power Query

Los pasos aplicados para transformar la tabla con datos acumulados en una tabla con datos diarios, con 4 columnas: país, fecha, valor acumulado y valor diario de fallecimientos, son explicados a continuación:

1. Índice agregado: Se agrega columna de índice desde 0

Como en la imagen, se selecciona desde el menú: Agregar columna > Columna de índice > Desde 0 

Con lo que se crea la fórmula M: 

#"Índice agregado" = Table.AddIndexColumn(#"Columnas con nombre cambiado", "Índice", 0, 1, Int64.Type), 

 

2. Índice agregado1: Se agrega columna de índice desde 1

Como en la imagen, se selecciona desde el menú: Agregar columna > Columna de índice > Desde 1 

Con lo que se crea la fórmula M: 
#"Índice agregado1" = Table.AddIndexColumn(#"Índice agregado", "Índice.1", 1, 1, Int64.Type),  

 

3. Consultas combinadas: Combina índices y crea tablas indexadas

Como en la imagen, se selecciona desde el menú: Inicio > Combinar consultas 

Con lo que se crea la fórmula M: 
#"Consultas combinadas" = Table.NestedJoin(#"Índice agregado1", {"Índice"}, #"Índice agregado1", {"Índice.1"}, "Índice agregado1", JoinKind.LeftOuter),  

 

En la siguiente imagen se selecciona la misma consulta en la segunda tabla.  En la primera tabla se selecciona la columna Índice En la segunda tabla se selecciona la columna Índice.1 Se presiona la tecla: Aceptar  

 

4. Se expandió Índice agregado1: Se expande el valor de la tabla indexada

Como en la imagen, en la columna Índice agregado1 se selecciona el icono de la derecha (con 2 flechas, una para cada lado) y se selecciona solamente la columna: Value 

Con lo que se obtiene el valor de la fila anterior y se crea la fórmula M: 
#"Se expandió Índice agregado1" = Table.ExpandTableColumn(#"Consultas combinadas", "Índice agregado1", {"Value"}, {"Índice agregado1.Value"}),  

 

5. Filas ordenadas: Se ordena por el índice desde 0

El paso anterior ha desordenado las filas, por lo que este paso las volverá a ordenar. Como en la imagen, en la columna Índice se selecciona el icono de orden ascendente.

Con lo que se crea la fórmula M: 
#"Filas ordenadas" = Table.Sort(#"Se expandió Índice agregado1",{{"Índice", Order.Ascending}}),  

 

6. Personalizada agregada: Diferencia entre el valor y el valor anterior

Ahora ya tenemos el valor acumulado de una fila y el valor acumulado de la fila anterior, por lo que se puede calcular la diferencia que será el valor diario. 

Se selecciona desde el menú: Agregar columna > Columna personalizada

En la siguiente pantalla se escribe la fórmula para calcular la diferencia: 

= [Value]-[Índice agregado1.Value] 

con el nuevo nombre de columna: Difference 

Con lo se crea la fórmula M: 

#"Personalizada agregada" = Table.AddColumn(#"Filas ordenadas", "Difference", each [Value]-[Índice agregado1.Value]),  

 

7. Columna condicional agregada: Cuando las filas son de distinto país, si la diferencia <0 entonces 0

La diferencia de cada fila también resta valores de países distintos. Este paso crea una columna condicional para que, cuando en cada fila la diferencia es negativa por ser de países distintos, sea de valor cero, de lo contrario mantiene la diferencia calculada en el paso anterior.
Se selecciona desde el menú: Agregar columna > Columna condicional
En la siguiente pantalla se selecciona la columna Difference, con operador es menor que, con valor 0, entonces 0. De lo contrario: Difference
Con lo se crea la fórmula M: 
#"Columna condicional agregada" = Table.AddColumn(#"Personalizada agregada", "Increment", each if [Difference] < 0 then 0 else [Difference]),  

 

8. Columnas quitadas: Quita todas las columnas auxiliares

Las columnas auxiliares que se han ido creando en los pasos anteriores ya no hacen falta, por lo que se pueden quitar.
Se seleccionan las columnas: Índice, Índice.1, Índice agregado1.Value, Difference
Se hace clic con el botón derecho del ratón sobre una de esas columnas, para mostrar el menú contextual, y se hace clic en: Quitar columnas 

Con lo se crea la fórmula M: 
#"Columnas quitadas" = Table.RemoveColumns(#"Columna condicional agregada",{"Índice", "Índice.1", "Índice agregado1.Value", "Difference"}),  

 

9. Tipo cambiado: Cambia el tipo de valor a número

Este paso cambia el tipo de la columna Increment para que sea del tipo entero. Se hace clic con el botón derecho del ratón sobre esa columna, para mostrar el menú contextual, se selecciona Cambiar tipo y se hace clic en: Número entero 

Con lo se crea la fórmula M: 
#"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas quitadas",{{"Increment", Int64.Type}}),  

 

10. Errores reemplazados: Reemplaza error por 0 en la fila 1

En la columna Increment hay un error en la primera fila debido a que no existe el valor anterior para calcular la diferencia, por lo que hay que reemplazar ese error. 

Se selecciona la columna Increment y se selecciona desde el menú: Transformar > Reemplazar los valores > Reemplazar errores 

En la ventana para reemplazar errores se escribe el valor 0. 

Con lo se crea la fórmula M: 

#"Errores reemplazados" = Table.ReplaceErrorValues(#"Tipo cambiado", {{"Increment", 0}})

 

Con este último paso se han conseguido obtener los valores diarios en la columna Increment, a partir de los valores acumulados de la columna Value, como se pretendía. Ahora ya tenemos una tabla con los países y los valores diarios y acumulados cada día de fallecimientos oficiales debidos al Covid-19.

¡Ya están listos los datos para comenzar a visualizar la carrera de muertos por el Coronavirus!

Vídeos de la carrera con Power Query

En los siguientes vídeos se explica cómo generar la tabla de datos acumulados y diarios con Power Query partiendo de la consulta a la Web:

En el próximo artículo explicaré cómo convertir esta tabla en un gráfico con la carrera de barras en Excel.