Tutorial de Microsoft Power Query sobre cómo solucionar problemas mixtos de ancho de columna fijo

Mudassir: Por hoy, tenemos un problema muy interesante con el que trabajar. El problema de este archivo es que se fija delimitado por columnas y no se como solucionarlo con Microsoft Power Query. Puedes ver el video completo de este tutorial en la parte inferior de este blog.

Primero, no fue fácil para mí eliminar las columnas dinámicamente. En segundo lugar, en este informe, tenemos una tabla con un ancho de columna diferente y luego otra tabla con un ancho de columna diferente.

Entonces, si aplico un delimitador dinámicamente en la parte superior, no podría obtener los datos de manera ordenada. Quiero obtener todos estos datos de la segunda tabla y mis números de producto de la primera tabla. También quiero el número de trabajo en todas y cada una de las filas de todas las tablas.

Traté de resolverlo por mi cuenta, pero como esto tiene algo que ver con la consulta de energía, necesitaba la ayuda de Melissa. Pensé que le llevaría al menos dos días, pero se las arregló para encontrar una solución de inmediato.

Melissa nos mostrará cómo resolvió este complicado problema. Creo que la mayoría de las personas se enfrentarán a este tipo de problemas y buscarán formas de resolverlos.

Melissa: El primer consejo es que si está viendo un archivo de longitud fija, puede ir a la pestaña Ver y activar la opción Monoespaciado .

Podemos ver que es una fuente de longitud fija. También podemos ver las cabeceras, tablas iniciales y subtablas. Estas son las partes que nos interesan y de las que queremos extraer.

Además, asegúrese de tener habilitada la barra de fórmulas. Siempre es bueno tenerlo visible en la pantalla, ya que lo usaremos con frecuencia para realizar ligeras modificaciones en la entrada.

Creé un parámetro para la ubicación del archivo donde almacené el archivo CSV. Lo incorporé como un archivo de preparación y lo sustituí en mi parámetro de ubicación de archivo. Luego creé una referencia y trabajaré a partir de esa referencia. Así que eso es lo que estamos viendo en este momento en Microsoft Power Query.

Tabla de contenido

• Agregar una columna de índice
• Creación de una lista de búfer en Microsoft Power Query
• Dividir encabezados de las filas
• Eliminar los espacios en blanco y nulos en Microsoft Power Query
• Dividir las secciones
• Recortar las cadenas de texto en Microsoft Power Query
• Conclusión

Agregar una columna de índice

Por lo general, cuando empiezo a trabajar en un archivo como este, necesito conocer los requisitos del cliente. Pregunto qué necesita el cliente y qué buscar.

En este caso, queremos el número de artículo y el número de trabajo de los encabezados, y luego queremos todos los detalles que pertenecen a ese encabezado específico.

Necesitaremos una llave para volver a juntar esas cosas. Pero si no hay una clave presente, mi opción es agregar una columna de índice . Haré clic en el ícono de la minitabla, seleccionaré Agregar columna de índice y luego agregaré Desde 0 .

Usaré una lógica más adelante con listas, que tienen el índice basado en 0. Tener su índice comenzando desde cero en realidad significa que puede hacer referencia a la misma fila. De lo contrario, tendrás que restar 1 para llegar a esa posición basada en 0.

Luego, necesitamos encontrar la posición donde están nuestros encabezados, lo que podemos hacer con bastante facilidad porque esos encabezados se repiten constantemente a lo largo de todo el archivo.

Para empezar, copiemos este valor:

Agregue una nueva consulta en blanco, péguela y llámela HeaderID .

Haré el mismo proceso para las subtablas. Copiaré esa cadena de texto, crearé otra consulta en blanco y pegaré ese valor. Esta será la cadena que usaremos cuando busquemos filas detalladas.

Si este proceso cambia de alguna manera el encabezado de cualquiera de estas tablas, todo lo que tengo que hacer es cambiar una de las cadenas de texto y el archivo volverá a funcionar.

Realmente no tengo que sumergirme en el código M para buscar esa cadena que estamos buscando. Solo podemos usar esto como un parámetro.

Habilitemos la carga para estas dos consultas.

Creación de una lista de búfer en Microsoft Power Query

Lo primero que haré es convertir Column1 en una lista haciendo referencia y cargándola en la memoria una vez. De esta manera, no tengo que hacer llamadas repetidas al archivo.

Abriré el editor avanzado y lo colocaré en la parte superior. Cuando utilice la interfaz de usuario para crear su código, hará referencia al paso anterior.

Cuando coloca un paso de búfer en cualquier otro lugar de su código y desea realizar una modificación, más adelante, le ayudará a realizar los cambios en el paso que está creando manualmente.

Llamaré a esto BufferList y haré referencia a Column1. Para cargarlo en la memoria, agregaré un paso List.Buffer .

Aquí está mi variable hasta el final en la parte superior. Puedo mencionarlo una y otra vez.

Lo primero que quiero determinar es dónde comienzan mis encabezados porque necesito una clave para mantener estas secciones de encabezado y obtener un valor único para todas esas filas. Para hacer esto, agregaré una columna personalizada y la llamaré Encabezado .

Escribiré que si Column1 es igual a nuestra ID de encabezado, entonces quiero que mi número de índice sea nulo.

Como resultado, encontró el texto y devolvió 5 y 23.

Necesito ese valor en todas las filas, así que necesito completarlo. Puede simplemente hacer clic derecho para completar, pero también puede usar una sintaxis muy simple y agregarla en la barra de fórmulas.

En este caso, agregué Table.FillDown y en la cadena de texto, indiqué qué columna queremos llenar (Header).

Ahora, lo hemos rellenado para todas las filas. Tenemos una clave para todas las secciones de encabezado y todas las secciones de fila porque todas comparten este valor.

Dividir encabezados de las filas

El siguiente paso es dividir los encabezados de las filas. Agregaré otra columna personalizada y la llamaré Temp . Esta vez, haremos algo más elaborado y aprovecharemos esa BufferList que creé anteriormente.

Usaremos un par de funciones de listas para ver cada una de las posiciones y encontrar si hay una coincidencia con el índice.

Comenzaré con una declaración if y usaré List.Contains para buscar una posición específica en BufferList y hacer referencia a la consulta HeaderID .

Queremos encontrarlo en toda la longitud del archivo y luego devolver la posición del elemento dentro de la lista. Si coincide con el índice, tenemos una coincidencia para esa fila específica.

Luego quiero devolver un valor para identificar el encabezado. En este caso, solo devuelvo una H. Copiaré la sintaxis para no tener que volver a escribirla.

También necesitamos identificar la sección de la fila. Si la lista no contiene HeaderID , sino DetailID , entonces estamos en una sección de fila.

Si Column1 es una cadena de texto vacía, quiero que permanezca vacía . Si ese no es el caso, entonces quiero que sea nulo .

Esta oposición obtuvo la fila de encabezado y devolvió una H, y luego encontró una fila detallada y devolvió una R. Luego devolvió 0 para todos los elementos que se comparten dentro de esa sección de fila.

Estos espacios en blanco o nulos son importantes porque le permiten completar. Rellenar no se moverá a través de esas celdas en blanco para que podamos eliminarlas más adelante.

Haremos esto en la barra de fórmulas y usaremos Table.FillDown nuevamente. Quiere una lista con el nombre de la columna, que es nuestra columna Temp .

Ahora tenemos los valores H y R repetidos en toda esta columna, lo que significa que podemos dividir los encabezados de las secciones detalladas.

También puede completarlo desde la interfaz de usuario si no desea escribir el código. Puede simplemente hacer clic con el botón derecho y seleccionar Rellenar , luego Abajo .

Eliminar los espacios en blanco y nulos en Microsoft Power Query

Ahora que tenemos este derecho, podemos eliminar las cosas que no necesitamos. Todo lo que es nulo o contiene un espacio en blanco son las filas que no necesitamos y deben eliminarse. Podemos eliminarlos filtrando.

Dividir las secciones

Una vez que eliminamos esos espacios en blanco y nulos, nos queda todo lo que necesitamos. En este punto, podemos simplemente dividir las secciones. Podemos centrarnos en las filas de encabezado y seleccionarlas porque tienen un espacio separado de todas las filas de detalles (que también tienen un espacio separado).

Agregaré un nuevo paso en la barra de fórmulas que me permitirá crear otro filtro en esa misma columna. En este caso, solo mantendré todas las secciones de encabezado.

Ahora, tengo todas esas filas de encabezados aquí.

Puedo seleccionar Column1 , ir a la barra de fórmulas, seleccionar Dividir columna y luego dividir por posiciones.

Luego, haz que Power Query lo resuelva por sí mismo. Sugerirá un par de posiciones. Haga clic en Aceptar para aceptar esas posiciones.

Lo único que nos interesa de los encabezados es el artículo y el número de trabajo .

Dentro de la barra de fórmulas aquí, puedo cambiar el nombre de aquellos con Item y Job # . Esto me salvará de otro paso de cambiar el nombre de la columna.

Después de este paso, todo lo que tengo que hacer es seleccionar el artículo , seleccionar el número de trabajo y, por supuesto, seleccionar nuestra tecla de encabezado . Luego eliminaré todas las demás columnas porque ya no las necesito.

This will be the result. We still need to clean up the values and remove the text item and dashes. All we want is those values in between.

So we open this up and unselect the dashes and items.

Now, all the headers are done.

We have to do the same process for DetailID too. I’ll need to rename those steps so that it will be easier for me to go back to it a little later.

We’ll traverse back to the initial query that we started. We started with Filtered Rows in the Applied Steps pane.

I’m going to copy this and add it to my filter. This time, I’m not selecting H but I’m selecting the R.

Then I’ll select Column1, go to the Split column, split by positions, then have power query figure it out.

This is what power query suggests. Let’s give this a try.

This actually looks pretty good. Even the total rows split up perfectly. Of course, there are a lot of spaces because we had that indentation.

Trimming The Text Strings In Microsoft Power Query

I’ll select that first column, then press Down + Shift to select until Column 1.10. Go to Transform, select Format, then Trim. Trimming will only remove the excess spaces in front or at the end of the string, not in between.

Next, we can just promote the headers, so I don’t have to type all of the headers or the titles for these columns. In the split step, I renamed two columns. Now, of course, with 10 columns, that’s a bit of a bother.

We also have to get rid of those excess values. Because we have totals, I have to use one of these last three columns because they’re the only rows that have the additional values somewhere in between. Then we’ll deselect those blanks, dashes, and texts.

Then I’ll remove the unnecessary columns so all that remains is a table with only the headers and only the details. We need a key to bring those sections back together again.

For that, we can use a self-merge so we can merge the table with itself to bring that information back together. On the Home tab, select Merge, then select Column 5 and the same query.

Instead of AllDetails, I want AllHeaders as my initial table that I want to merge with.

Esto ha recuperado toda la información de la tabla de encabezado con una sola fila para cada artículo y cada número de trabajo.

Usamos una clave para fusionar con las filas de detalles. Si presiono hacia un lado en el espacio en blanco aquí, veremos una vista previa de todas las filas que pertenecen al encabezado 5.

Eliminaremos la columna final aquí y luego terminaremos de corregir el ancho de columna fijo mixto en Microsoft Power Query.

Conclusión

En este tutorial, encontramos una forma de resolver problemas mixtos de ancho de columna fijo utilizando Microsoft Power Query. Si disfrutó del contenido cubierto en este tutorial en particular, no olvide suscribirse al canal de televisión LuckyTemplates.

Tenemos una gran cantidad de contenido que sale todo el tiempo de mí y una variedad de creadores de contenido, todos los cuales están dedicados a mejorar la forma en que usa LuckyTemplates y Power Platform.

Toronjil