Power BI / Power Query y Archivos de Excel: formatos XLSX vs XLS y tipos de datos

Este es un tema que anteriormente ha sido tratado aquí por mi buen amigo Ken Puls, pero fue hasta hace poco que encontré esta respuesta de Ehren sobre cómo Power Query interpreta los archivos xls de manera diferente a los archivos xlsx y ciertamente vale la pena una publicación en el blog para cubrir esto con más detalle.

Te recomiendo que leas esta publicación si usas Power Query dentro de Excel o Power BI con archivos de Excel en *.xls o *.xlsx

Los datos dentro de los archivos

Puedes descargar ambos archivos y la consulta final haciendo clic en el siguiente botón para que puedas seguir el ejemplo:

Ambos archivos tienen exactamente los mismos datos. La única diferencia es que uno se guarda como un archivo XLS y el otro como un archivo XLSX.

¿Qué datos tenemos dentro de los archivos? Bueno, tenemos 3 columnas:

Columna Date

Tiene fechas, pero están formateadas usando el formato de celda ‘dd-MMM-YY’ como se muestra en la siguiente imagen:

Columna Code

Tiene valores numéricos, pero están formateados como texto usando el formato de celda ‘00000’ como se muestra en la siguiente imagen:

Columna Value

Tiene números decimales, pero están formateados como moneda sin decimales como se muestra en la siguiente imagen:

El aplicar un formato a las celdas es una práctica común en los archivos de Excel. Formateas el valor de una celda únicamente para facilitarle al usuario final la lectura de dichos valores, pero debajo de esa máscara de formato está el valor real.

Como Power Query interpreta los Datos dentro de diferentes extensiones de archivos de Excel

Aquí es donde es importante señalar la distinción de cómo se comporta Power Query al conectarse a diferentes extensiones de archivos de Excel.

Dale un vistazo a cómo lucen tus datos cuando llegan a Power Query, dependiendo de la extensión del archivo:

Como sabemos por los comentarios de Ehren, Power Query utiliza un mecanismo diferente para obtener los datos de estas dos extensiones de archivo.

¿Pero qué tan diferentes son estos mecanismos? Bueno, en términos simples, cuando te conectas a un archivo *.xls, estarás importando los «valores enmascarados» o básicamente la «celda formateada» y no el valor real de una celda. Con un archivo *.xlsx es todo lo contrario, obtienes los valores de cada celda y el formato no se toma en consideración.

Aquí hay una tabla en la que se compara cómo Power Query interpreta los datos de los dos archivos que tenemos.

Sugerencias generales

Dependiendo de lo que necesites, puedes convertir tu archivo de un archivo xlsx a un xls o al revés, pero si, por ejemplo, necesitas arreglar tus datos de alguna manera, siempre puedes usar las operaciones de conversión de Power Query para cambiar el tipo de datos de una columna de texto a numérico, de texto a fecha y demás, pero esto no eliminará las situaciones en las que podrían faltarte datos debido al formato, por ejemplo, como en nuestro caso en el que formateamos los valores numéricos para no tener decimales, pues cuando nos conectamos a un archivo xls perdemos completamente los decimales. En esos casos, es mejor eliminar el formato o cambiar la extensión a un xlsx.

Hay muchas más ventajas en el uso de las extensiones modernas de archivo *.xlsx (como el uso de tablas), por lo que te recomiendo que pruebes y uses esa extensión siempre que sea posible. Si no es posible, entonces quizás tu sistema podría generar un archivo plano (csv o incluso txt) que sería más fácil de manejar.