Tutorial 3 Power Query - Carrera de Barras

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.

Tutorial 2 Power Query - Carrera de Barras

En la primera parte de este tutorial vimos cómo extraer datos con Power Query:

Tutorial 1 Power Query - Carrera de Barras

Ahora veremos la parte más interesante de esta excelente herramienta ETL - Extract, Transform and Load.

Cómo transformar los datos de la carrera

La transformación de los datos de la carrera de barras para los 10 países con más fallecimientos oficiales por Covid-19 consta de dos partes:

1. Transformar la tabla dinamizada con una columna por día en una tabla normalizada con sólo 3 columnas: país, fecha y valor acumulado de fallecimientos. Esta transformación se explica en este artículo.
2. Transformar la tabla normalizada anterior para calcular el valor diario de fallecimientos a partir del valor acumulado, mediante un truco que contaré más adelante. Esta transformación se explicará en el próximo artículo.

Cómo transformar la tabla dinamizada en normalizada

Con un solo paso se podría transformar la tabla en normalizada: "Anulación de dinamización", aunque realmente hacen falta aplicar unos cuantos pasos más:

1. Columnas quitadas1:  Latitud y Longitud que no se usarán en la carrera.
2. Anulación de dinamización: Este es el paso más importante pues normaliza la tabla para que cada Atributo y Valor estén en una fila distinta.
3. Fechas convertidas: Convierte formatos de fecha americana "m/dd/aa" en fechas.
4. Columna quitada: Quita columna de Atributo con las fechas americanas.
5. Tipo cambiado a fecha: Columna como tipo fecha. Columna de valor como número.
6. Columnas reordenadas:  A la derecha las columnas de fecha y valor.
7. Filas agrupadas: Se agrupa por país y fecha, sumando los valores.
8. Columnas con nombre cambiado: La primera columna es el país.

En esta imagen se pueden ver los 8 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:

// Quitadas las columnas de latitud y longitud. #"Columnas quitadas1" = Table.RemoveColumns(#"Encabezados promovidos",{"Lat", "Long"}), // Normaliza la tabla para que cada Atributo y Valor estén en una fila distinta. #"Anulación de dinamización" = Table.UnpivotOtherColumns(#"Columnas quitadas1", {"Province/State", "Country/Region"}, "Atributo", "Value"), // Convierte formatos de fecha americana "m/dd/aa" en fechas. #"Fechas convertidas" = Table.AddColumn(#"Anulación de dinamización", "Date", each Date.FromText([Atributo],"en-US")), // Quita columna de Atributo con las fechas americanas. #"Columna quitada" = Table.RemoveColumns(#"Fechas convertidas",{"Atributo"}), // Columna como tipo fecha. #"Tipo cambiado a fecha" = Table.TransformColumnTypes(#"Columna quitada",{{"Value", Int64.Type}, {"Date", type date}}), // A la derecha las columnas de Fecha y Valor. #"Columnas reordenadas" = Table.ReorderColumns(#"Tipo cambiado a fecha",{"Country/Region", "Province/State", "Date", "Value"}), // Se agrupa por país y fecha, sumando los valores #"Filas agrupadas" = Table.Group(#"Columnas reordenadas", {"Country/Region", "Date"}, {{"Value", each List.Sum([Value]), type nullable number}}), // La primera columna es el país #"Columnas con nombre cambiado" = Table.RenameColumns(#"Filas agrupadas",{{"Country/Region", "Country"}}),

Pasos aplicados para transformar la carrera

La tabla dinamizada de la que partimos tiene cientos de columnas hacia la derecha, con una columna por día, desde el 22 de enero de 2020 hasta el día de la descarga actualizada, como muestra esta imagen después del paso de Encabezados promovidos:

Los pasos aplicados para transformar la tabla dinamizada, con una columna por día, en una tabla normalizada, con sólo 3 columnas: país, fecha y valor acumulado de fallecimientos, son:

1. Columnas quitadas1:  Latitud y Longitud que no se usarán en la carrera.

Los valores de geolocalización no son necesarios para la carrera por lo que, después de promover los encabezados, se seleccionan las columnas Lat y Long. Se hace clic con el botón derecho del ratón en la cabecera y se presiona: Quitar columnas

2. Anulación de dinamización: Este es el paso más importante pues normaliza la tabla para que cada Atributo y Valor estén en una fila distinta.

Los valores acumulados están en cientos de columnas, una columna por día. En este paso se transforman en dos columnas con cientos de filas: una columna para el día y otra columna para el valor acumulado, lo que normaliza la tabla.

Se seleccionan las dos columnas de la izquierda y en el menú Transformar se selecciona: Anulación de dinamización de otras columnas

Con ello se consigue transformar más de 400 columnas en 2 columnas, anulando la dinamización, con lo que se consigue transformar esta tabla dinamizada:

En esta otra tabla normalizada, perfecta como origen de datos de una tabla dinámica. Parece una paradoja que teniendo inicialmente los datos dinamizados haga falta anular la dinamización para luego aplicarles otra dinamización. No es ningún contrasentido pues la dinamización final no es la misma que la dinamización original:

3. Fechas convertidas: Convierte formatos de fecha americana "m/dd/aa" en fechas.

El paso anterior aplicado ha transformado las fechas en una única columna denominada Atributo, con formato texto de fecha americana, o sea los meses antes que los días.

Este nuevo paso transforma la fecha americana, de la columna Atributo, en fecha manejable por Power Query en una nueva columna: Date

Para ello se debe seleccionar en el menú: Agregar columna y luego: Columna personalizada, con lo que aparece esta ventana en la que hay que editar la fórmula = Date.FromText([Atributo],"en-US") y presionar el botón Aceptar:

4. Columna quitada: Quita columna de Atributo con las fechas americanas.

Como ya no hace falta, se selecciona la columna Atributo, se hace clic con el botón derecho del ratón para mostrar el menú contextual, y se hace clic en: Quitar

5. Tipo cambiado a fecha: Columna como tipo fecha y columna de valor como número.

Se selecciona la columna Date, se hace clic con el botón derecho del ratón para mostrar el menú contextual, y se cambia su tipo a Fecha.

Además se selecciona la columna Value, se hace clic con el botón derecho del ratón para mostrar el menú contextual, y se cambia su tipo a Número entero.

  

6. Columnas reordenadas:  A la derecha las columnas de fecha y valor.

Se selecciona la columna de la izquierda y se desplaza como segunda columna. Se selecciona la columna de la derecha y se desplaza como tercera columna.

7- Filas agrupadas: Se agrupa por país y fecha, sumando los valores.

Se seleccionan las columnas de país y de fechas y, en el menú Inicio, se hace clic en: Agrupar por, con la operación Suma de valores, se edita el nuevo nombre de columna como Value, y se presiona el botón: Aceptar, con lo que los datos de estados se sumarizan en cada país.

8. Columnas con nombre cambiado: La primera columna es el país.

A la primera columna se le cambia el nombre: Country.

Con lo que ya tenemos por fin la tabla normalizada con sólo 3 columnas: país, fecha y valor acumulado de fallecimientos.

¡Objetivo cumplido con Power Query!

En la 3ª parte de este tutorial explico cómo transformar la tabla normalizada anterior para calcular el valor diario de fallecimientos a partir del valor acumulado:

Tutorial 3 Power Query - Carrera de Barras

Tutorial 1 Power Query - Carrera de Barras

Cómo extraer, transformar y cargar datos de la carrera

En el artículo anterior publiqué una Carrera de Barras en un Gráfico Excel que se puede descargar desde aquí:

Carreras de Barras por Covid-19 en Excel v2

En este artículo explicaré cómo obtener los datos desde un archivo en formato CSV con una serie de datos por país y día de los fallecimientos oficiales acumulados debidos al Covid-19.

Si sigues leyendo aprenderás varias cosas:

1. Cómo buscar datos de origen en un Repositorio de Datos alojado en la Web.
2. Cómo consultar con Power Query un archivo de Internet que consolida y actualiza diariamente los datos.
3. Cómo extraer de ese archivo sus datos dinamizados, transformarlos en datos normalizados y cargarlos en una tabla, con la ayuda de Power Query que es una excelente herramienta para extraer, transformar y cargar datos (en inglés: ETL - Extract, Transform and Load).
4. Cómo transformar los datos acumulados en datos diarios mediante un truco en Power Query.

Este artículo es una lección práctica del uso de Power Query para preparar datos para nuestros informes. Lo que antes había que hacerlo con macros VBA ahora es mucho más fácil de hacer con esta herramienta integrada por Microsoft en muchas versiones de Excel.

Se trata de transformar datos dinamizados, con cientos de columnas, en una tabla normalizada con muy pocas columnas, como se puede apreciar en esta imagen:

Los datos dinamizados originales se han insertado en una tabla con más de 400 columnas. Las 4 primeras columnas identifican la geolocalización, el resto de columnas informan de los fallecimientos acumulados en una serie de días creciente con la actualización diaria de los datos originales.

Los datos transformados se han insertado en una tabla con únicamente 4 columnas y miles de filas para informar: País; Fecha; Valor Acumulado y Valor Diario. Este último dato se ha calculado a partir de los valores acumulados gracias a un truco que luego comentaré.

La tabla normalizada sirve perfectamente como origen de datos de las tablas dinámicas auxiliares con las que se ha generado el gráfico de barras con el que se visualiza la carrera en Excel.

Cómo buscar el origen de datos de la carrera

Lo más importante para hacer un buen análisis y una buena visualización es la obtención de datos fidedignos de fuentes confiables. Normalmente los departamentos TI suministran las consultas desde bases de datos relacionales a las que hay que conectarse, pero en este caso los datos están en Internet.

Cuando me propuse obtener los datos de fallecimientos globales por Covid-19 busqué en Internet por la frase: "Covid-19 data repository"

Encontré una página del Gobierno USA y otra del Centro de Ciencia e Ingeniería de Sistemas de la Universidad Johns Hopkins (JHU CSSE) que comprobé que era consultada globalmente en muchos medios de información.

Los datos acumulados están en las siguientes páginas de https://github.com/:

COVID-19 Data Repository by the JHU CSSE

COVID-19/tree/master/csse_covid_19_data

csse_covid_19_time_series 

time_series_covid19_deaths_global.csv

Esta última página contiene un botón "Raw" con los datos brutos que son los datos originales necesarios para este proyecto:

Raw - time_series_covid19_deaths_global.csv

Es obligado citar al siguiente artículo de Lancet, publicado por JHU CSSE, para cualquier uso de estos datos en una publicación:

https://www.thelancet.com/journals/laninf/article/PIIS1473-3099(20)30120-1/fulltext

Si se quieren analizar más datos de mortalidad, se puede consultar esta página:

https://coronavirus.jhu.edu/data/mortality

Cómo extraer los datos de la carrera

Voy a explicar paso a paso cómo extraer los datos del archivo con la serie temporal de fallecimientos globales por Covid-19: Raw - time_series_covid19_deaths_global.csv

1) Abrir un libro en blanco con una de las versiones de Excel que incluyen Power Query.

2) En el menú de la cinta de opciones seleccionar: Datos

3) Hacer clic en el botón de la izquierda: Obtener datos

3) Seleccionar: Desde otras fuentes

4) Hacer clic en: Desde la web

5) En la siguiente pantalla se introduce la Dirección URL de la página web que apunta al archivo CSV:

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv

6) Se presiona el botón: Aceptar 

    Aparece la siguiente ventana:

7)  Como Anónimo se presiona el botón: Conectar

     Se obtiene una vista previa del archivo CSV, con delimitador: Coma

8) Se presiona el botón: Transformar datos

9) Se abre la ventana del Editor de Power Query, que sirve para crear o modificar consultas.

A la derecha de la imagen, en la Configuración de la consulta, aparecen 2 PASOS APLICADOS.

Para la transformación de los datos se añadirán posteriormente más pasos a la consulta. Ahora vamos a analizar estos primeros dos pasos, comenzando por el último:

• Tipo cambiado: Transforma los tipos de 482 columnas en texto. 

= Table.TransformColumnTypes(Origen,{{"Column1", type text}, ... , {"Column482", type text}})

Como una vez transformada esta tabla no tendrá todas esas columnas, no tiene sentido cambiar el tipo de un número fijo de columnas. ¡Este paso se elimina dándole a la cruz de la izquierda!

• Origen: Extrae el contenido de un documento en formato CSV desde la web con la dirección URL entrecomillada:

= Csv.Document(Web.Contents("https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv"),[Delimiter=",", Columns=482, Encoding=65001, QuoteStyle=QuoteStyle.None])

Como cuando se actualiza la consulta el número de columnas crece en una más cada día que pasa, el argumento marcado en negrita sobra y se debe eliminar de la fórmula M: Columns=482, 

En la documentación de Funciones M de Power Query - Csv.Document se indica que:

Cuando no se especifica, el número de columnas vendrá determinado por lo que se encuentre en la entrada

Por lo que el paso correcto para Origen será sin el argumento Columns:

 = Csv.Document(Web.Contents("https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None])

Este cambio no cambia nada en los datos extraídos en esta consulta pero marca la diferencia al actualizar la consulta de nuevo, pues no va a depender de un número fijo de columnas.

Esta sintaxis nueva es la sintaxis del Lenguaje de fórmulas de Power Query M (enlace aquí) que se debe aprender si queremos olvidarnos de las macros para consultar y transformar datos. En muchos casos el lenguaje de fórmulas M sustituirá ventajosamente al lenguaje de macros VBA. 

La ventaja principal es que, en lugar de usar la grabadora de macros y modificar las macros grabadas, usaremos el Editor de Power Query para generar la mayoría de los pasos aplicados en una consulta, sin necesidad de modificar casi nada de las fórmulas M generadas automáticamente por Power Query.

Ahora ya podemos dar nuestro primer paso voluntario en Power Query:

• Encabezados promovidos: Como vimos en el Editor de Power Query, los encabezados están en la primera fila, por lo que hay que hacer lo que indica esta imagen:

Usar la primera fila como encabezado

= Table.PromoteHeaders(Origen, [PromoteAllScalars=true])

Con este paso los encabezados de la fila 1 han pasado a ser los títulos de las columnas:

Este paso ha vuelto a cambiar los tipos de las columnas, por lo que hay que volver a eliminar el paso siguiente: 

• Tipo cambiado: Transforma los tipos de 482 columnas en texto.

Con lo que, de momento, únicamente habrá 2 pasos en esta consulta:

Estos dos pasos se pueden ver en lenguaje M de Power Query con el Editor avanzado. Dentro del Editor de Power Query, en Vista, Editor avanzado:

Estas son las fórmulas M de los dos pasos programados hasta ahora:

let // Datos de fallecimientos por Covid-19 en el mundo. // Se han eliminado el número de columnas a extraer por defecto. // COVID-19 Data Repository provided by Johns Hopkins University Center for Systems Science and Engineering (JHU CSSE). // https://github.com/CSSEGISandData/COVID-19/ Origen = Csv.Document(Web.Contents("https://github.com/CSSEGISandData/COVID-19/raw/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]), // Las cabeceras de la tabla estaban en la primera fila. #"Encabezados promovidos" = Table.PromoteHeaders(Origen, [PromoteAllScalars=true]),

Sólo queda cerrar el Editor de Power Query y presionar el botón: Mantener

Se inserta en una nueva hoja denominada time_series_covid19_deaths_glob una tabla con los datos de la consulta.

Se debe guardar el archivo Excel como de costumbre.

Lo hecho hasta ahora se podría haber hecho, en versiones sin Power Query, con una simple conexión en Excel, pero al hacerlo con Power Query nos abre un mundo de transformaciones automáticas de los datos que son difíciles de imaginar con las fórmulas de Excel. Pero las nuevas transformaciones de datos las veremos en la 2ª parte de este artículo.

Con lo dicho hasta ahora se puede dar por finalizada la explicación de cómo extraer datos de fallecimientos acumulados por el Covid-19. Como este artículo ya es bastante extenso, dejaré para próximos artículos:

Cómo transformar los datos de la carrera

En la 2ª parte del tutorial explico cómo transformar una tabla dinamizada de muchas columnas, una columna por cada día, en una tabla normalizada con una columna para la fecha y otra para el valor acumulado:

Tutorial 2 Power Query - Carrera de Barras

En la 3ª parte del tutorial explico cómo transformar los datos acumulados en datos diarios:

Tutorial 3 Power Query - Carrera de Barras

Cómo cargar los datos de la carrera

En la 4ª parte del tutorial explico cómo convertir la tabla en un gráfico con la carrera de barras.

Carreras de Barras por Covid-19 en Excel v2

Descarga esta cruenta carrera de barras, montada en un gráfico de Excel, con los 10 países con mas fallecimientos oficiales durante esta terrible pandemia por coronavirus.

Descarga la carrera

• Microsoft OneDrive: 

Gráfico_Carreras_Barras_Fallecimientos_Covid-19_PW2.xlsm

• Sites Google Drive: 

Gráfico_Carreras_Barras_Fallecimientos_Covid-19_PW2.xlsm

Con la versión 2️⃣ de este Gráfico 🔝🔟 de Carreras 🏃‍♂️ de Barras 📊 he generado este GIF animado con un retardo de 5 segundos:

En el siguiente enlace puedes descargar la 1ª versión de este gráfico y conocer de dónde proceden los datos acumulados originales:

Gráfico Carrera Barras Excel Bar Chart Race

Cómo ver la carrera

1. Hacer clic en el icono Covid-19 🔅 para cargar datos actualizados.
2. Seleccionar un día 🗓 de comienzo de la carrera en la escala de tiempos.
3. Hacer clic encima del gráfico 🔝🔟📊 para arrancar y parar la carrera.

Características de la carrera

1. Que la carrera 🔝 se actualice al hacer clic en el icono 🔅 Covid-19
2. Que haciendo clic encima del gráfico pueda arrancar y parar la carrera.
3. Que la carrera sea por acumulado de ☠ hasta un día, con el símbolo: Σ - Sigma o Sumatorio.
4. Que la carrera sea por número diario de ☠, con el símbolo: # - Almohadilla o Numeral.
5. Que la carrera 🔝 sea por días 🗓; por semanas o por meses 📅
6. Que se pueda cambiar la velocidad ⏳ de la carrera: 1 - mínima a 10 - máxima.
7. Que haya listas de países predefinidos 🌍
8. Que haya países con barras rojas ⛔
9. Que detecte el lenguaje 👂 del país para geolocalizar la plantilla por el idioma.
10. Que vaya a los datos de la fecha más reciente cargada haciendo clic en el título de encima del gráfico.
11. Que se pueda elegir la fecha de comienzo de la carrera en la escala de tiempos.

Vídeo de carreras

En este vídeo explico cómo usar el interfaz gráfico de usuario de la plantilla con las carreras de barras en Excel.

En los siguientes artículos explicaré cómo se hizo esta carrera en un gráfico de barras en Excel, en 3 tutoriales sobre Power Query y en un tutorial sobre el gráfico:

Tutorial 1 Power Query - Carrera de Barras | #ExcelPedroWave

Tutorial 2 Power Query - Carrera de Barras | #ExcelPedroWave

Tutorial 3 Power Query - Carrera de Barras | #ExcelPedroWave

Tutorial Gráfico Carrera de Barras en Excel | #ExcelPedroWave

Gráfico Carrera Barras Excel Bar Chart Race

Oscura carrera

Los Interfaces Gráficos de Usuario incorporan cada vez más temas oscuros para reducir la fatiga ocular en entornos de poca luz, lo que agradecemos quienes tenemos la vista cansada de tanto jugar con las hojas de cálculo y con las pantallas.

Este es el primer tema oscuro que preparo en Excel.

Es muy oscuro por dos motivos:

1. Por usar colores oscuros sobre fondo gris, con datos en color negro y barras negras, para visualizar una negra carrera de muerte.
2. Por mostrar el lado más oscuro del bicho Covid-19 (enlace aquí) con las series de datos de fallecimientos en un gráfico muy oscuro.

Esta oscura plantilla en Excel muestra un Gráfico de Carrera de Barras, conocido en inglés como Bar Chart Race, con esta forma oscura:

Instrucciones del lado oscuro:

1. Clic en el icono Covid-19: descarga datos nuevos y refresca todo.
2. Seleccionar una fecha en la Escala de Tiempo.
3. Elegir todos o algunos Países en la segmentación izquierda.
4. Seleccionar Países Rojos en la segmentación derecha.
5. Clic en el Gráfico de Barras para Arrancar/Parar la Carrera.
6. Clic en el título del gráfico para ver datos de la última fecha.

Características oscuras del gráfico:

Este gráfico está construido sobre 3 hojas de cálculo de Excel:

• Hoja con la serie de fechas de fallecimiento por Covid-19 en cada país, como origen oscuro de los datos, que se carga con una consulta en Power Query.

• Hoja 'TD_Países' con 3 tablas dinámicas que transforman oscuramente los datos de muertes:
• TD_Total_Países: Con los datos acumulados de fallecimientos por países hasta el día elegido.
• TD_Países: Con los datos anteriores filtrados para los países elegidos.
• TD_Lista_Países: Lista ordenada de países filtrada para el país rojo seleccionado.
• Una tabla Tabla_TOP10_Países: Con los 10 países que se verán en el gráfico oscuro.

• Hoja 'Fallecimientos Covid-19' con la visualización oscura de:
• Un gráfico de carrera de 10 barras horizontales, con la posición de cada país en la carrera y el número acumulado de fallecidos para un día determinado. Clic en el Gráfico de Barras para Arrancar/Parar la Carrera.
• Un título oscuro con el número total de fallecidos hasta el día elegido. Al hacer clic sobre el título de arriba aparecen los datos del último día cargado.
• Una Escala de tiempo para elegir un día desde el 22 de enero de 2020 hasta el último día cargado. Pinchar el icono del bicho para actualizar los datos.
• Una Segmentación de datos oscuros para seleccionar los países del gráfico.
• Una Segmentación de datos para elegir países con la barra en color rojo.

• Hoja 'Country Codes' con los códigos de los países, para marcar en rojo el país correspondiente al sistema operativo la primera vez que se descargan datos. Canadá no aparecerá porque tiene el mismo código que US y aún no está resuelto el problema de códigos repetidos de algunos países.

Origen oscuro de los datos

Con Power Query extraigo, transformo y cargo los datos (ETL - Extract, Transform and Load). Los datos se extraen de un archivo CSV que se puede descargar navegando por la siguiente página:

https://github.com/CSSEGISandData/COVID-19/

Podrás refrescar diariamente los datos de fallecimientos oficiales a nivel mundial por Covid-19, que no los muertos reales ni los fallecimientos derivados de los efectos colaterales de la pandemia, como la baja o nula atención sanitaria a otras enfermedades o accidentes graves, debido al colapso hospitalario.

El propietario del Repositorio de Datos COVID-19 es el Centro de Ciencia e Ingeniería de Sistemas de la Universidad Johns Hopkins (JHU CSSE), y publica en el siguiente enlace un archivo CSV con los datos globales de fallecimientos por el coronavirus:

time_series_covid19_deaths_global.csv

Este archivo es el origen de datos del gráfico de barras, por lo que hay que citar al siguiente artículo de Lancet, publicado por JHU CSSE, para cualquier uso de estos datos en una publicación:

https://www.thelancet.com/journals/laninf/article/PIIS1473-3099(20)30120-1/fulltext

Descarga la plantilla más oscura

Como esta oscura plantilla es muy especial, porque nunca se había visto una carrera parecida en Excel, la descarga se hará bajo demanda, para lo que tienes que solicitarla escribiendo un comentario debajo de este artículo del blog, indicando el motivo por el que quieres descargarla.

IMPORTANTE: Incluye en tu comentario la Clave de la celda D6 (en fondo naranja) que aparece al introducir un Usuario en la celda D5 (en fondo amarillo).

En una respuesta te mandaré la Contraseña que tendrás que introducir en la celda D7 (en fondo verde) para ver los enlaces de descarga: Clic para descargar desde Microsoft OneDrive o desde Google Drive:

En el siguiente enlace puedes descargar la 2ª versión mejorada de este gráfico:

Carreras de Barras por Covid-19 en Excel v2

Gracias por visitar mi blog y por comentar si te gusta o disgusta...