Cuando trabajamos con PowerBI para analizar los datos de una empresa, no siempre tenemos la suerte de contar con un DataWareHouse al que conectarnos, ni siquiere a veces tenemos una simple base de datos con información. Son muchas las ocasiones en las que nos encontramos con decenas, centenas o incluso miles de ficheros excel o txt que contienen la información histórica de los datos a los que queremos tener acceso. La técnica que vamos a describir a continuación está descrita con mucho más detalle en el libro «Collect, Combine and Transform Data Using Power Query in Excel and PowerBI» del gran Gil Raviv».

Para solucionar esos casos, afortunadamente disponemos en PowerQuery de un conector Carpeta que nos permite conectarnos a una ruta e importar de forma automática todos los ficheros que contiene.
Veamos paso a paso como realizar esta operación con este ejemplo.
Partimos de la base que tenemos en una carpeta local una carpeta llamada MisDatos dentro de nuestra unidad C:\ y esta carpeta contiene 2 ficheros Excel.

Cada uno de estos ficheros contiene información referente a usuarios de nuestra compañía pero como podemos observar en la siguiente imagen, las columnas de los ficheros no tienen ni los mismos nombres ni están en las mismas posiciones.

Este hecho provoca que si ejecutamos el conector Carpeta disponible, la importación se realizará de forma incorrecta ya que PowerQuery no será capaz de asociar que columna del fichero1 corresponde con que columna del fichero2, generando así un fichero resultante de la unión de ambos incorrecto.

Para poder realizar la importación de forma correcta necesitamos en primer lugar, crear un fichero que llamaremos conversion.xlsx. Este fichero deberá contener una fila con cada una de las asociaciones de columnas de los ficheros que estamos importando. Este paso lo vamos a realizar de forma manual y debería ser como el siguiente ejemplo. En la primera fila los nombres «erróneos» y en la segunda fila los nombres «correctos».

Cuando tengamos el fichero generado lo cargaremos en PowerQuery (nosotros la llamaremos tblConversión) tal como muestra la imagen.

A partir de este punto, vamos a generar una nueva Consulta en blanco dónde utilizando un poco de código M y alguna manipulación desde el interface lograremos generar una única tabla con la unión de estos 2 ficheros que recordemos, no tienen ni las columnas con el mismo orden ni están en la misma posición.

Comenzamos accediendo al menú Nuevo Origen más opciones y seleccionamos Carpeta

En el menú que aparecerá, seleccionaremos la carpeta donde se encuentren los ficheros que queremos importar.

Seguidamente, aparecerá una ventana con la lista de los ficheros existentes en la carpeta indicada y en toda la estructura de subcarpetas que tenga (que para nuestro caso son solo 2 ficheros).

Pulsaremos en transformar.

Eliminaremos ahora todas las columnas existentes menos Content y pulsaremos en la doble flechita de la cabecera de la columna para proceder a combinar los archivos.

En nuestro caso, al tratarse de un fichero EXCEL seleccionaremos el nombre de la hoja que contendrá la información que queremos importar.

Al aceptar, veremos que se crea la estructura completa de la importación de contenidos de ficheros de una carpeta, pero que el resultado de la importación es incorrecto porque como sabemos, los nombres de las columnas y el orden de las mismas no es coincidente en los 2 ficheros importados.

Para resolver esta problemática, aplicaremos un poco de magia en lenguaje M sobre la función personalizada que se ha generado de forma automática con la importación de carpetas.

De esta forma, vamos a seleccionar la query Transformar archivo de ejemplo, vamos a pulsar en la barra de fórmulas sobre el icono fx para agregar un nuevo paso, vamos a borrar ese paso y vamos a escribir el siguiente código:

= Table.TransformColumnNames(#»Encabezados promovidos», each try Table.Column(tblConversion, _){0} otherwise _)

Este código va a transformar los nombres de las columnas del paso «Encabezados promovidos».
La función Table.TransformColumnsNames tiene como entrada una tabla «#Encabezados promovidos» y como segundo parámetro una función que sirve para aplicar la transformación. Esa función personalizada la conseguimos con los comandos each y _ que son los comandos que se utilizan como shortcut para definir una función personalizada.
Si revisamos la función Table.Column, es una función donde le vamos a indicar una tabla y una columna y nos va a devolver una lista con los valores de esa columna indicada. El código utilizado Table.Column(tblConversion, _){0} lo que está haciendo es,
por cada columna de #»Encabezados promovidos» ves a la tblConversion, busca la columna _ (que es el parámetro que contiene para cada columna de #»Encabezados promovidos» el nombre de esa columna. Si lo encuentra en tblConversión, retorna la lista de valores pero solo la primera fila {0}, recordemos que M es un lenguaje basado en 0. Por tanto, si el nombre de la columna que estamos buscando del fichero importado, es igual a alguno de los nombres de las columnas del fichero tblConversion (esto significaría que el nombre NO ES CORRECTO), retorna el valor de la primera fila de esa columna (que sería el nombre correcto que queremos para la columna), en caso contrario (como hay definido un try — otherwise), retorna _ que sería el nombre actual de la columna del fichero importado ya que es correcto y no es necesario cambiarlo por la primera fila de tblConversion.

Quedando el código completo de la query, vista desde el menú de editor avanzado de la siguiente forma:

Como se puede observar ahora desde la Query MisDatos, ahora la importación se ha realizado correctamente.

Espero que os sea de utilidad

Toni Jurado