Ya he mencionado que mi abuelita solía decir que cuando tenemos un martillo todo problema se parece a un clavo. Power Query es mi "martillo" a la hora de resolver un problema de transformación de datos en Excel.
El "clavo" de turno era crear un calendario, similar al del post anterior, pero con horas. Es decir un fila para cada hora de cada día. En lugar de 365 o 366 filas, 8760 o 8784 si el año es bisiesto.
El método es similar al del post mencionado pero con ciertos cambios. Como queremos que nuestra solución sea dinámica, empezamos por definir un conexión a una celda donde ponemos el año para el cual queremos crear el calendario
Esta consulta la guardamos como "solo conexión" (ver el post anterior). El código es
Supongamos que queremos crear un calendario con Power Query (¿por qué queríamos hacer semejante cosa? Bien, hay varios motivos pero no son el tema de este post).
Aparentemente la tarea es bien sencilla. Power Query tiene un "atajo" para crear Listas: {x..y} donde x e y son números enteros. Como sabemos, las fechas están representadas por números enteros de manera que si sabemos que el 01/01/2020 es el número 43831 y el 44196 el 31/12/2020, hacemos lo siguiente:
1 - abrimos una consulta en blanco
2 - en la barra de las fórmulas escribimos ={43831..44196} lo cual crea un serie aritmética de razón 1
En mi post anterior sobre clasificación (ranking) con Excel y con Power Query mostré un método de desempate basado en un "sorteo", es decir, asignar a cada miembro de la lista un número aleatorio y determinar la posición en caso de empate de acuerdo a ese número.
Como vimos, si usamos la función Number.Random de Power Query todas las filas de la tabla reciben el mismo número.
Buscando en la Internet dí con esta discusión donde se muestran algunas soluciones (aunque la única que funcionó con mi ejemplo fue a basada en List.Random).
Debo confesar que no entiendo cabalmente la solución propuesta que muestro en el post. Esto me produjo un sentimiento de incomodidad que me llevó a buscar mi propia solución.
Recordemos el problema a solucionar:
tenemos un a lista de calificaciones de alumno;
queremos ordenarla por calificación, de mayor a menor;
en caso de "empate" (dos o más alumnos con la misma calificación), realizamos un sorteo para determinar la posición relativa de los "empatados";
el sorteo lo hacemos calculando un número aleatorio para cada alumno. Luego ordenamos los empates de acuerdo al número aleatorio que recibió cada alumno.
La solución que desarrollé esta basada en la función Number.Random y en un "truco": combinar la consulta consigo misma.
Estos son los pasos aplicados partiendo de esta tabla de datos:
Un docente me consulta sobre como ordenar una lista de alumnos de acuerdo a sus calificaciones. El problema del docente era cómo determinar la posición (ranking) de cada alumno en caso de empate (es decir, dos o más alumnos con la misma calificación).
Según Wikipedia hay cinco métodos de calcular la posición de un elemento en una lista (ranking)
Ordinal simple (1,2,3,4): cada elemento recibe un número de clasificación secuencial sin tomar en cuenta "empates".
Competencia estándar (1,2,2,4): los elementos que "empatan" reciben el mismo número de clasificación, y luego se deja un hueco en los números de clasificación.
Competencia modificada (1,3,3,4): la clasificación se hace dejando los huecos en los números de clasificación antes de los conjuntos de elementos de igual rango (en lugar de después de ellos como en la clasificación estándar de la competencia).
Clasificación Densa (1,2,2,3): los artículos que se "empatan" reciben el mismo número de clasificación, y los siguientes artículos reciben el número de clasificación inmediatamente posterior.
Clasificación fraccionada (1,2.5,2.5,4): los artículos que "empatan" reciben el mismo número de clasificación, que es la media de lo que tendrían en las clasificaciones ordinales.
En un próximo post volveré sobre el tema de los métodos de ranking. En este post me centraré en el método ordinal simple y algunas variaciones. Digamos que ésta sea la lista de alumnos:
Esta nota viene a colación de una consulta sobre cómo calcular posiciones (ranking) por grupos con Power Query, tema sobre el cual publiqué en el pasado este post mostrando como hacerlo con Excel.
La consulta original era, dada una serie de datos agrupados por grupos, cómo calcular la posición (ranking) de cada elemento dentro del grupo usando Power Query.
Consideremos esta tabla de datos
Nuestra tarea es ordenar por volumen de ventas y de mayor a menor, las ventas mensuales en cada área.
Como toda tarea a realizar con Power Query empezamos por crear una consulta
Los cálculos de tiempo en Excel tienen sus bemoles como ya hemos visto por ejemplo en el post anterior. Excel (y no sólo Excel) usa una serie de números enteros para representar las fechas. Excel tiene dos años-base distintos para los cálculos de tiempo: 1900 y 1904 de manera que el 1 representa el 01/01/1900 o el 01/01/1904 según el caso.
Fueran cuales fueran las razones (ver el comentario de Jaime en el post mencionado) la base 1900 conlleva un serio problema: Excel considera al año 1900 como bisiesto cuando no lo es (y recordemos que por defecto la base de cálculo es 1900).
Podemos ver este bug haciendo este ejercicio:
creamos una columna con una serie de números enteros del 31 al 61;
copiamos esta serie en la columna contigua.
en la segunda columna cambiamos el formato a Fecha.
Vemos la correspondencia entre el número de serie (31, por ejemplo) y la fecha (31 de enero 1900). Si nos fijamos en el final de nuestra serie vemos el problema: Excel reconoce la fecha 29 de febrero 1900
pero esta fecha ¡no existe! 1900 no es una año bisiesto.
El problema con este bug es que influye en el cálculo de lapsos. Por ejemplo si calculamos
01/02/1900 - 31/01/1900
obtenemos, naturalmente 1; pero si calculamos
01/03/1900 - 28/02/1900
obtenemos, erróneamente, 2.
Es fácil entender por qué Microsoft nunca corrigió este bug.¿Cuántas veces tuvieron que calcular diferencias de fechas que involucren el año 1900?.
Todo empezó cuando un colega en el trabajo copió una tabla de fechas de un cuaderno Excel a otro. Era uno de esos raros días en que su atención esta centrada más en el trabajo que en cosas agradables.
Con sorpresa observó que las fechas cambiaban al ser copiadas de un cuaderno al otro. Esta es la situación (por supuesto que no se trataba de fechas de nacimiento)
Después de rascarse repetidamente el cuero cabelludo, acción que, está por demás decir, no incrementa la capacidad analítica del rascado, decidió consultarme.
Debo reconocer que tuve unos momentos de embarazosa vacilación hasta dar con la clave del misterio. Excel tiene dos bases distintas para los cálculos de fecha: 1900 y 1904.
Como (casi) todos sabemos Excel usa una serie de números enteros para representar las fechas. La base por defecto (para todas las versiones de Excel para Windows) es el 1 de enero del 1900, fecha representada por el número 1. El número 2 representa el 2 de enero de 1900 y así sucesivamente.
Pero sucede que Excel permite también usar como base el año 1904. De manera que el mismo número de serie puede representar dos fechas distintas dependiendo de las definiciones del cuaderno Excel.
De esta manera, al copiar las fechas Excel "traduce" el número de serie a distintas fechas.
En la imagen de arriba podemos ver que la diferencia entre las fechas en los cuadernos es de 4 años; más precisamente 1462 días.
Cambiamos la base en Archivo-Opciones-Avanzadas-Al calcular este libro
En esta captura de pantalla podemos ver cambian las fechas al cambiar el año-base
Volviendo al caso de mi colega, ¿cómo remediar esta situación?
Una posibilidad es cambiar la definición en el cuaderno de mi colega. Esta solución no es recomendable ya que si hay otras fechas en el cuaderno, éstas cambiaran también.
Otra posibilidad es poner el número 1462 en alguna celda vacía y copiarla; luego seleccionar el rango de fechas a modificar y usar Pegado Especial-Valores-Restar para corregir la fechas.
¿Por qué conviven estas dos bases en Excel? Aparentemente por motivos de compatibilidad con las versiones de Excel para Mac. En "Mac" la base es 1904 (la primer versión de Excel fue para Mac).
Como no fuera esta dualidad es problemática y puede conducir a errores importantes.
