Traducir el blog
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:
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:
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:
- Índice agregado: Se agrega columna de índice desde 0
- Índice agregado1: Se agrega columna de índice desde 1
- Consultas combinadas: Combina índices y crea tablas indexadas
- Se expandió Índice agregado1: Se expande el valor de la tabla indexada
- Filas ordenadas: Se ordena por el índice desde 0
- Personalizada agregada: Diferencia entre el valor y el valor anterior
- Columna condicional agregada: Cuando las filas son de distinto país, si la diferencia <0 entonces 0
- Columnas quitadas: Quita todas las columnas auxiliares
- Tipo cambiado: Cambia el tipo de valor a número
- 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:
¡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.
Mi lista de blogs
-
Ordenar Hojas en Excel usando el Ordenamiento Burbuja - Ordenar hojas en Excel puede ser una tarea crucial para organizar y visualizar datos de manera efectiva. En este artículo, […] The post Ordenar Hojas en ...Hace 7 horas
-
Can Excel Handle Piping Pressure Drop Calcs? - If you’ve ever found yourself grappling with the task of calculating pressure drops in piping systems, you’re in the right place. In this post, I’ll […]Hace 12 horas
-
How to Format Cells in Microsoft Excel - If you’re wondering how to format cells in Excel to make your worksheets look good, you’ve come to the right place! When you type numerical values or text ...Hace 1 día
-
What's New in Excel (April 2024) - Welcome to the April 2024 update. Generating multiple formula columns, creating complex formula columns that span across multiple tables, and new ways t...Hace 2 días
-
Semiprimos suma de los primeros - (Ver entradas anteriores sobre números semiprimos) Cualquier número elegido al azar no tiene que ser suma de los primeros números de cierto tipo, como pr...Hace 3 días
-
PLEASE UPDATE THE RSS FEED - The RSS feed URL you're currently using https://follow.it/excelunusual will stop working shortly. Please add /rss at the and of the URL, so that the URL ...Hace 1 semana
-
PLEASE UPDATE THE RSS FEED - The RSS feed URL you're currently using https://follow.it/excel-foro-ejercicios-ejemplos-soluciones-dudas will stop working shortly. Please add /rss at t...Hace 1 semana
-
PLEASE UPDATE THE RSS FEED - [image: PLEASE UPDATE THE RSS FEED] The RSS feed URL you're currently using https://follow.it/jldexcelsp will stop working shortly. Please add /rss at the...Hace 1 semana
-
Excel Dependent Drop Down Lists – Block Changes - One of my favourite Excel features is a data validation drop down list. In just a couple of minutes, you can make a list of items, then make that list appe...Hace 1 semana
-
Excel Power Query: Obtener y transformar - Iconos más importantes de Obtener y transformar de Excel 2016: Obtener y transformar (Power Query) de Yolanda Cuesta Altieri Excel Power Query: Obtener y...Hace 1 semana
-
CONTROLAR LOS TIEMPOS EN NUESTRAS APLICACIONES DE POWER APPS CON EL TEMPORIZADOR - Hola a todos! Cuanto realizamos programaciones es normal el uso de funciones para pausar la ejecución del código un determinado tiempo o para programarlo...Hace 1 semana
-
Let, Lambda, and py_Eval - I have covered the new Excel Let and Lambda functions before at: Two New Functions; Let and Lambda Eval and Let examples Lambda and VBA The Lambda function...Hace 1 semana
-
Conversor PGN de ajedrez mejorado - 🔝*Select the language of this blog post in the Google box at the top left. * Esta es la *4ª versión del Conversor PGN de ajedrez* que hago, cada uno ...Hace 2 semanas
-
Announcing Power BI Dashboard Contest (win $500 prizes!) - Hey there, I have a SUPER exciting announcement! April is about to get a whole lot sweeter with our Power BI Dashboard Contest! Your mission, should you ...Hace 3 semanas
-
Minutos de juego y puntos. El Espanyol, sus finales de partido y mis enfados - Pienso que el Espanyol este 2024 se está dejando muchos puntos al final de los partidos. Cuando el ... Leer más » The post Minutos de juego y puntos. El ...Hace 4 semanas
-
TikTok’s search evolution - 2 in 5 Americans use TikTok as a search engine. Nearly 1 in 10 Gen Zers are more likely to rely on TikTok than Google as a search engine. More than half of...Hace 1 mes
-
Aprende cómo usar la función #Filtrar con rangos de fechas SIN macros 📋 [VIDEO] - ¿Alguna vez te has preguntado cómo puedes filtrar tus datos por fechas específicas en Excel de manera rápida y eficiente? En este video, voy a enseñarte pa...Hace 1 mes
-
MASTERCLASS Gratis – Gráfica de Gestión Proyectos en #EXCEL. - Aprende a crear un Gráfico de CURVA S, ideal para GESTIÓN DE PROYECTOS, porque te permite identificar como esta tu proyecto tanto en COSTOS como en TIEMP...Hace 2 meses
-
Interés compuesto con Excel - En la imagen, se presenta un esquema de las distintas funciones Excel para calcular el valor del dinero en el tiempo mediante la capitalización compuesta...Hace 3 meses
-
Demos cursos de Excel 2007, 2010, 2013, 2016, 365 - Puedes consultar las demostraciones de los siguientes capítulos de los cursos Excel. Demo cursos ExcelHace 3 meses
-
El sistema operativo no está configurado para ejecutar esta aplicacion. - Si esta semana habéis recibido el mensaje "El sistema operativo no está configurado para ejecutar esta aplicacion" cuanto intentásteis abrir alguna aplic...Hace 7 meses
-
Atajos esenciales de Excel que todo profesional debe conocer - Manejar Excel con destreza puede ser el superpoder que no sabías que necesitabas. Agiliza tus tareas diarias y sorprende a tus colegas con estos atajos c...Hace 8 meses
-
Unblocking and Enabling Macros - When Windows detects that a file has come from a computer other than the one you're using, it marks the file as coming from the web, and blocks the file....Hace 1 año
-
-
Navigating Outlook Favorites - I have these four favorites defined in Outlook: From the inbox, I could hit Shift+F6 to get into the Favorites area but sometimes I would end up in no man’...Hace 1 año
-
Progress on the Block Protocol - Since the 1990s, the web has been a publishing place for human-readable documents. Documents published on the web are in HTML. HTML has a little bit of… Re...Hace 1 año
-
Decálogo para realizar Trabajos de Fin de Grado (TFGs) y de Fin de Máster (TFMs) - 1.- Tanto TFGs como TFMs son un requisito para graduarse a la vez que una oportunidad para aprender. Así, el tiempo que se le dedica es muy variable. De...Hace 2 años
-
London Excel Meetup Workbooks - The workbooks used in my presentation on “Analytical and Interactive Dashboards in Excel” at the London Excel Meetup, September 3, 2020Hace 3 años
-
Dimensiones-Propiedades de perfiles de acero (IMCA) - Saludos compañeros, en esta oportunidad compartimos esta Planilla Excel que contiene las Propiedades y dimensiones de diferentes tipos de perfiles de acero...Hace 4 años
-
-
-
-
-
-
No Response to "Tutorial 3 Power Query - Carrera de Barras"
Leave A Reply
Dime si te gusta lo que lees y, si no te gusta, dime por qué. Tengo habilitada la moderación de comentarios. Tu comentario se publicará pronto.