Porqué usar colores para codificar información en una hoja de cálculo es mala idea (parte 2)

Otra de las razones para no usar la codificación mediante colores de información en una hoja de cálculo es que he filtrado por colores es propio de Microsoft Excel.

Por lo tanto, si pasamos nuestra hoja con colores, a un usuario que use otro programa de hoja de cálculos, cómo puede ser LibreOffice Calc, este no podrá filtrar la información que le estamos mostrando y tendrá que limitarse a ir pasando las celdas para mirar los datos.

Cómo podemos ver en Microsoft Excel sí que se muestra la opción de filtrar por colores.

Seleccionando un color podremos ver solo las filas correspondientes de ese mismo color.

Una situación todavía peor sería que enviaremos nuestra hoja de cálculo ya filtrada.

Dado que LibreOffice Calc, no tiene filtrado por colores se da la peculiaridad de que tampoco permite eliminar el filtrado, que no permitiría acceder al resto de los datos de la hoja.

En la siguiente imagen os muestro una hoja de cálculo filtrada por el color naranja en Microsoft Excel y luego abierta en LibreOffice Calc.

Porqué usar colores para codificar información en una hoja de cálculo es mala idea (parte 1)

Ayer os traía el ejemplo de una hoja de cálculo que me habían hecho llegar en la que se codifica a el año de edición de los libros mediante una leyenda de colores, como si fuese un gráfico.

Aparte de la enorme cantidad de tiempo que puede ser precisa para codificar un listado de esa manera, sin cometer errores, vamos a analizar qué otras cosas pueden ir mal.

Por ejemplo, no todas las hojas de cálculo ni formatos de fichero soportan la misma cantidad de colores.

Comencemos con el mismo ejemplo.

A día de hoy sigo recibiendo gran cantidad de ficheros en formato Microsoft Excel 2003, los de la extensión XLS.

Una de las características de este formato, es que solo soportaba 64 colores.

Por lo tanto, si alguien guarda nuestro fichero en ese formato, verá aparecer la siguiente imagen.

En Cristiano, esta imagen viene diciendo que se reducirá la paleta de colores, usando solamente los que soporta el formato XLS.

Dependiendo de los colores elegidos, puede:

1 - No pasar nada.

2 - Que los colores cambien por otros.

3 - Que los colores más parecidos se fusionen.

En los dos primeros casos no es demasiado problema, pero el tercero produce una perdida de información.

Filtrado por gran cantidad de colores en Microsoft Excel

Hoy he recibido una hoja de cálculo, que seguramente fué realizada con gran esfuerzo y buena intención, pero con un resultado algo peligroso.

De trataba de una hoja de cálculo con información de libros.

El año de edición se había codificado usando un color de fondo distinto para cada año.

La información del año se aportaba a modo de leyenda, en unas columnas al lado.

A continuación una simulación de la mencionada hoja.

Cuando se aplica un filtro, solo se muestran, por defecto, los primeros cinco colores que encuentra.

En caso de que encuentre más colores, se muestra el texto "Más colores de celda..."

Si pulsamos esa opción se nos muestra el resto de los colores.

Pudiendo de esta forma filtrar por cualquier color.

Sin embargo este método para codificar información mediante el color de las celdas no es aconsejable, ya que además de incómodo de crear es difícil de comprender.

fds

fd

df

df

dff

Promover encabezados en Power Query, para Microsoft Excel y Power BI

El segundo paso que se suele realizar al importar datos con Power Query, tanto para Microsoft Excel como para Power BI, es promover la primera fila como encabezados.

Esto hace que los datos de la primera fila, se traten como nombres de columna y no como datos.

De no hacerlo, todas las columnas serían identificadas como texto.

En lenguaje M la instrucción para realizar este proceso es Table.PromoteHeaders.

Ejemplo para Microsoft Excel:

= Table.PromoteHeaders(Origen)

Ejemplo para Power BI:

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

En este caso, para ver esta instrucción, deberemos seleccionar el paso denominado "Encabezados promovidos" que, usualmente, es el segundo paso de la importación.

En la imagen el segundo paso para Excel.

Y la instrucción correspondiente.

El segundo paso para Power BI.

Y la instrucción.

Aunque difieren ligeramente ambas instrucciones, es posible usar cualquiera de las dos en ambos casos.

Cambio de fichero cargado mediante lenguaje M en Microsoft Power BI

Ayer veíamos la instrucción para cargar un fichero CSV con lenguaje M en Power BI.

En caso de tener que aplicar el mismo proceso a un fichero con un nombre distinto o ubicado en otra carpeta, es posible modificar la carga del fichero con solo modificar la instrucción de carga del fichero.

Para ellos solo es preciso modificar la ruta del fichero, el resto de los cambios se aplicaran sobre los datos del nuevo fichero.

En este caso solo hemos modificado el texto resaltado en rojo.

Fichero original (todo.csv):

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todo.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])

Fichero secundario (todoModificado.csv):

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todoModificado.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])bi

El proceso es sencillo.

Primero abrimos el archivo de Power BI.

Luego debemos editar la consulta desde el menú contextual del botón derecho.

La instrucción que se nos muestra se corresponde al paso que tengamos seleccionado en Power BI, que por defecto, es el último realizado.

Seleccionamos el primer paso, para que se nos muestre la instrucción en lenguaje M correspondiente a la carga del fichero CSV.

Antes de la modificación vemos los datos del primer CSV.

Modificamos el nombre del fichero CSV e, importante, pulsamos la tecla intro, para aplicar el cambio.

Inmediatamente se cargan los.datos del nuevo fichero.

Es un truco útil cuando se pretende aprovechar parte de los pasos de un fichero.

Carga csv en power query para Microsoft Excel y Power Bi

Si una herramienta es interesante en el mundillo del análisis de datos esta es Power Query y su lenguaje M.

Esto se debe principalmente a que es una herramienta común tanto a Microsoft Excel como a Power BI.

Hoy os traigo la instrucción que generan Excel y Power BI para cargar el mismo fichero CSV.

Microsoft Excel:

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todo.csv"),[Delimiter=";",Encoding=1252])

Power BI:

= Csv.Document(File.Contents("C:\Blog\20210624cargaDatosM\todo.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])

Como podéis ver las diferencias son mínimas, tanto que cualquiera de los dos códigos funcionaría sin problema en la otra herramienta.

En Excel se hizo la importación desde "Obtener y transformar".

La fórmula se encuentra en el primer paso.

Y es exactamente la que os mostraba.

En Power BI, cargamos el fichero CSV desde "Obtener datos".

En la siguiente ventana pulsamos directamente en el botón "Cargar"

Y vemos que, también en el primer paso, se muestra la misma fórmula en lenguaje M.

En este caso la única diferencia, es que Power BI, tiene resaltado de color, como podéis ver en la captura.

ELT vs ETL en Microsoft Excel

En informática a la hora de cargar y tratar datos existen dos conceptos ETL y ELT.

ETL (Extract, Transform, Load)

ELT (Extract. Load, Transform)

En Microsoft Excel, lo más habitual es cargar todos los datos y luego filtrar los datos que se desean, lo que corresponde al proceso ELT (Extract. Load, Transform).

Para ello los cargamos desde "Obtener datos externos"

Este proceso tiene como inconveniente que se cargan datos que no interesan en el análisis, y que luego hay que filtrar o si se realizan cálculos, tenerlos en cuenta para no incluirlos en los cálculos.

Esto aumenta el tiempo de cálculo y reduce el número de filas de interés que podemos tratar.

En el peor de los casos, no podremos cargar todos los datos, ya que también se tendrán en cuenta datos que no interesan, pero que cuentan entre el millón de líneas que se pueden cargar en Microsoft Excel.

También se puede aplicar el método ETL (Extract, Transform, Load), mediante el uso del componente Power Query, que nos permite aplicar filtros y transformaciones a los datos antes de cargarlos.

Para ello se cargan los datos desde el grupo "Obtener y transformar".

Usando este método podremos aplicar las transformaciones a los datos originales, e importar un máximo de un millón de filas de datos de interés, tras haber eliminado los datos no necearios.

El método a utilizar dependerá de la cantidad de elementos del conjunto de dato original, y de lo importante que sea el tiempo de proceso de las fórmulas de la hoja de cálculo.