Anexar Datos de Archivos

Combinar, Consolidar o anexar cualquiera o múltiples txt, csv o archivos de Excel de una Carpeta

El Caso

Si alguna vez necesitas hacer cualquiera de las siguientes con sus datos:

  • Anexar
  • Consolidar
  • Combinar

con información que proviene de ya sea de Excel, texto o un archivo CSV, entonces esta es la mejor solución para hacerlo. En este patrón obtendrá la forma más óptima y sencilla de combinar sus archivos de una carpeta específica y combinarlos todos juntos si desea. Así es! combinar los datos de un TXT, CSV y Excel todos juntos. No hay límites en el tamaño del archivo o la cantidad de archivos a combinar – va simplemente a funcionar!

Descarga el Archivo

Nuestro Objetivo

Lo que necesitamos es una manera de extraer todos los datos de nuestros archivos como:

  • Archivos CSV
  • Archivos de Text
  • Archivos de Excel

y entonces de alguna manera agregar o consolidar todos esos datos en una tabla alta. Este era un escenario bastante complejo que podríamos resolver con VBA o SQL, pero ahora tenemos una manera amigable, más eficiente y fácil de hacer esto. No te olvides de descargar el libro de trabajo con el fin de seguir este artículo!

Paso 1: Descomprimir el Archivo y Explorarlo!

Antes de comenzar con el verdadero patrón, asegúrate de descargar el libro de trabajo con el fin de seguir adelante. El libro de trabajo viene con los archivos que se pueden ver en la imagen anterior dentro de una carpeta llamada PQExample que es básicamente la carpeta que vamos a utilizar como fuente para nuestra solución en Power Query. El archivo o libro de Excel que contiene la solución de Power Query se llama Ultimate Combination.xlsx y podrás verlo una vez descomprimas el archivo que has descargado en la carpeta principal. Te animamos a explorar todos los archivos y ver lo que hay dentro. En general, todos comparten los mismos nombres de encabezados de columna:

  • Product (Producto)
  • Date (Fecha)
  • Gross Sales (Ventas Brutas)
  • Amount (Monto)

Ahora podemos dirigirnos al verdadero patrón.

Paso 2: Encontrar la Consulta que hace la Magia

Una vez abras el archivo Ultimate Combination.xlsx, te darás cuenta que no tiene datos. La solución de Power Query se ha almacenado como una conexión única y espera de tu comando para cargar sus datos en el libro de Excel. Para poder ver esta consulta necesitarás ir a la cinta de Power Query, dar clic en el ícono Mostrar panel y luego a nuestra mano derecha veremos el panel de consultas con una única consulta bajo el nombre de Ultimate Combination. Le damos clic derecho a esa consulta y luego seleccionamos Editar para abrir la ventana del Editor de Consultas y analizar la solución. Te darás cuenta de inmediato que hay un error en la consulta, pero no te asustes. La razón detrás de este error es que dicha solución está apuntando a una carpeta local en mi (Miguel) computadora. Para solucionar esto simplemente tenemos que ir al paso bajo el nombre de Source y luego dar clic en el ícono de engranaje justo a la mano derecha del nombre Source. Esto nos brindará una nueva ventana con un botón para explorar dentro de nuestro disco local y encontrar la carpeta que contiene los archivos que deseamos combinar – todo esto dentro de una interfaz intuitiva y amigable. Encuentra el archivo que has descomprimido y selecciona la carpeta bajo el nombre de PQExample.

Y una vez hagas esto te darás cuenta que la solución comenzará a hacer su magia. Podrías terminar aquí y simplemente disfrutar del resultado final ya que todos los archivos han sido combinados (puedes dar clic en el paso con nombre Expanded para ver el resultado final), pero a cambio, vamos a ver cuan simple ha sido crear esta solución.

Entendiendo la Consulta

En vez de escribir un párrafo largo, hemos dividido esta consulta en secciones para que podamos entender cada paso de la consulta por su cuenta. Podrías hacer clic en las palancas para ampliar y ver el contenido y el colapso de nuevo si lo deseas.

Source

La forma en que se inicia la consulta es seleccionando la carpeta donde se guardan todos los archivos. Esto se hace fácilmente por la Cinta de Power Query como se muestra en la siguiente imagen: Una cosa a tomar en consideración es que PowerQuery también toma los archivos de cualquier subcarpeta, pero puedes filtrarlos utilizando el campo Ruta o Path.

Get the files

En este paso simplemente filtramos la extensión de archivo por lo que sólo obtenemos las siguientes extensiones:

  • extensión es igual a .csv
  • extensión es igual a .txt
  • extensión comienza por .xls

La forma en que harías esto es mediante la selección del icono de filtro en el campo y simplemente hacer un filtro como normalmente lo harías en Excel.

Removed Other Columns

En este caso, vamos a estar quitando algunas columnas que no necesitamos, pero en lugar de seleccionar las columnas que no queremos, vamos a estar seleccionando los que queremos y decirle a Power Query que sólo queremos mantener aquellas. Echa un vistazo a la siguiente imagen para encontrar el botón que hace el truco, pero asegúrate de seleccionar primero las columnas que deseas conservar.

Trans1

Aquí es donde tendremos que llegar a conocer un poco acerca de las funciones en Power Query. Sabemos que tenemos algunos archivos de Excel en nuestra consulta pero, ¿cómo extraemos los datos de ellos? Echa un vistazo a las columnas que tenemos disponibles. Te darás cuenta que tenemos una columna llamada Content que contiene un binario. Ese binario es el archivo en cuestión de Excel y con el fin de interpretar el binario necesitamos una función llamada Excel.Workbook() Utilizando la siguiente fórmula: if Text.StartsWith([Extension], ".xls") then Excel.Workbook( [Content]) else null tenemos una nueva columna que es básicamente la que me muestra todos los datos que el archivo de Excel (en cada fila) mantiene. Puedes hacer clic en cualquiera de los valores Table que se encuentran en la columna Custom para saber lo que hay dentro de cada una de ellos. Más a menudo, nos ocupamos de 3 tipos diferentes de datos dentro de un libro de Excel:

  1. Una Hoja (Sheet)
  2. Una Tabla (Table)
  3. Un Rango Definido (Named Range)

Nota: Te darás cuenta que en algunos archivos vamos tener tablas, hojas y en otros sólo vamos a tener solo hojas. Este es el momento donde se define si sólo queremos combinar las tablas, las hojas, los rangos con nombre o una combinación de ellos. Asegúrate de revisar que no se están combinando los mismos datos dos veces ya que una tabla es parte de una hoja y potencialmente podría ser combinada en el camino equivocado.

Trans2

Ahora necesitamos expandir la columna Custom para poder tener todos los datos del arhivo d Excel en nuestra Consulta y escoger los datos que necesitamos. el resultado de ello nos brinda 4 columnas:

  • Custom.Data = los datos actuales que se encuentran dentro del libro de Excel (representados como una tabla)
  • Custom.Name = el nombre de la hoja donde se almacenan los datos
  • Custom.Item = este es el nombre del elemento, si es un Nombre de Rango entonces va a ser el nombre de ese rango, para las hojas entonces va a ser el nombre de la hoja y para las tablas que va a ser el nombre de la tabla
  • Custom.Kind = es el nombre del objeto que se encuentra desde el libro de Excel. Los más comunes son Hoja, Tabla y DefinedName (Rango Definido o Nombre de Rango)

La Fórmula y otros Pasos

Hasta ahora hemos logrado extraer los datos del libro de Excel en objetos. Ahora tenemos que encontrar una manera de extraer los datos del archivo CSV y TXT y ser capaces de combinar esos con los datos encontrados en el archivo de Excel. Vamos a crear una nueva columna con esta fórmula que va a hacer el truco: if [Extension] = ".csv" then Table.PromoteHeaders(Csv.Document([Content])) else if [Extension] = ".txt" then Table.PromoteHeaders(Csv.Document([Content],null,"," )) else if [Custom.Kind] <> "Table" then Table.PromoteHeaders([Custom.Data]) else [Custom.Data] Nota: estamos suponiendo que el archivo txt está delimitado por una coma, pero puedes cambiar eso cambiando la coma en esta línea de código para otra cosa, como una barra vertical (|),barras (/ \), punto y coma u otro.

Csv.Document([Content],null,"," ) El resultado de esa fórmula nos da una columna que contiene sólo las tablas con las cabeceras correctas. Puedes tener la tentación de ampliar esta columna en este momento, pero no vamos a hacer eso por el momento. En su lugar, vamos a limpiar la tabla que tenemos actualmente y eliminar columnas innecesarias como Content y Custom.Data ya que todos los datos que necesito se almacenan en la columna Custom. También vamos a añadir una nueva columna que nos dará alguna información importante como el número total de filas sobre las tablas que estamos a punto de combinar. Usando esta fórmula obtenemos el número total de filas de las tablas: Table.RowCount([Custom]) y esto es lo que vemos en la sección Almost there.

Obtener Dinámicamente una lista de los Encabezados de cada Archivo

Fundamentalmente, eso es lo que la fórmula en el paso MyList está haciendo. Básicamente agarra todos los encabezados de todas las tablas que se encuentran en el paso anterior, los reúne en una tabla y luego crea una tabla de valores distintos. Más tarde transforma esa tabla en una lista ya que necesitaremos la lista más tarde. Dicha lista se convertirá en un parámetro para que podamos combinar dinámicamente todos los archivos sin tener en cuenta si tienen todos la misma estructura o no. Un caso de uso común para hacer algo como esto es que tal vez tenemos algunas columnas en algunos archivos, pero que no están presentes en otros, pero podría necesitarse esas columnas para un análisis específico y este patrón MyList hace precisamente eso. Crea dinámicamente una lista única de todos los encabezados encontrados al fondo de todas las tablas en el paso anterior. Aquí está el código: =Table.ToList(Table.RemoveColumns(Table.Distinct(Table.ExpandListColumn(Table.AddColumn(#"Almost there", "ColumnHeaders", each Table.ColumnNames([Custom])), "ColumnHeaders"),{"ColumnHeaders"}),{"Name", "Extension", "Custom.Name", "Custom.Item", "Custom.Kind", "Custom", "Custom.1"})) con el fin de insertar el código, tendrás que crear un paso personalizado. Puedes crear un paso personalizado al apretar el ícono fx en la barra de fórmulas. Una vez que activas ese paso personalizado, tienes que pegar el código anterior para hacer que funcione. ¿Cómo este Código hace eso? Vamos a explicar cómo hace lo que hace. En primer lugar, tenemos que extraer los encabezados de las columnas de cada tabla y con el fin de hacer esto creamos una nueva columna usando la función Table.ColumnNames como columna personalizada.El resultado debe de lucir así: y lo que tenemos que hacer ahora es simplemente ampliar esa columna ColumnHeaders mediante el icono junto al nombre de la columna que tiene el icono de flechas opuestas y el resultado de esto se verá así: como se puede ver en la imagen anterior, la acción de expandir básicamente creó una lista de todas las columnas encontradas en cada tabla. por lo que en el archivo CSV archivo.csv encontramos 8 encabezados y lo que queremos hacer ahora es simplemente crear una lista única de encabezados. Para ello, simplemente seleccione la columna que contiene los valores que queremos hacer únicos y hacemos clic en el botón de Quitar Duplicados que se encuentra en la pestaña Inicio. El resultado de esto se verá así: y lo que tenemos que hacer ahora es quitar todas las demás columnas, así que solo obtenemos una tabla de 1 columna que contiene los valores únicos de Columnas. Seleccionamos las columnas que queremos eliminar y seleccionamos eliminar columnas o, como se muestra en la siguiente imagen, seleccionamos las columnas que queremos mantener y hacemos clic donde dice Eliminar Otras columnas. el resultado de dicha operación debe de lucir del a siguiente forma: y ahora que tenemos esta tabla de 1 columna con los valores exclusivos para el columnas, nuestro trabajo es transformar esa tabla en una lista para que podamos utilizar esa lista como argumento para una función más adelante. Es por eso que usamos la función Table.ToList pero la única manera en este momento de hacerlo es mediante la creación de un paso personalizado (golpear el icono fx) e insertándolo manualmente y eso es básicamente lo que hace todo el código.

Los Últimos Pasos

Los 2 pasos finales:

  • Here we go
  • Expanded

El paso Here we go simplemente hace unos cambios de nombre de las columnas con sólo hacer doble clic al nombre de las columnas el paso Expanded simplemente expande la columna Custom al darle simple clic al ícono de flechas que van en dirección opuestas al lado del nombre de la columna. El resultado de esta solución / consulta debería de verse de la siguiente manera:

Cambiar la carga en Exceltodo lo que necesitas hacer ahora es simplemente seleccionar donde desearías cargar tus datos; ya sea en una nueva hoja de Excel o en el Modelo de Datos  

Patrones para Power Query
Taller de Power Query

este trabajo ha sido compartido 0 veces
 600
Sobre

 Power Query Training

  (13 Artículos)

Somos un equipo comprometido en ofrecer el mejor contenido para los nuevos y experimentados usuarios de una herramienta en la que creemos: Power Query. Estamos firmemente convencidos de que esta es la mejor herramienta que ha sido agregada a Microsoft Excel en un largo tiempo.

  • Jorge Saavedra

    Hola, cual podria ser la ventaja o desventaja de utilizar el comando cat para unir todos los archivos en uno solo, en vez de esta función de power query?

    Por otra parte, cual seria la mejor practica para unir varios archivos que sumados dan mas de 2 millones de filas?