LuckyTemplates Power Query: Eliminación de filas duplicadas

En este blog, le mostraré cómo eliminar filas duplicadas en LuckyTemplates Power Query según las condiciones. Puede ver el video completo de este tutorial en la parte inferior de este blog.

Este ejemplo es de una pregunta que se planteó en el . El miembro tiene datos transaccionales y quiere eliminar filas que se anulan entre sí.

Hay dos tipos transaccionales distintos, uno para entrada y otro para salida. Ella también agregó el conteo de movimientos. Y en base a eso, formuló tres escenarios.

El escenario uno se basa en dos filas. Si se cancelan entre sí, se deben eliminar ambas filas. Si ese no es el caso, entonces se deben mantener ambas filas.

Los escenarios dos y tres se basan en una combinación de filas, en parte anulándose entre sí. Esas filas deben eliminarse por orden de entrada, de modo que solo se conserven las últimas transacciones entrantes.

Básicamente, estamos repitiendo la misma lógica una y otra vez. Deberíamos poder diseñar un enfoque único que se ajuste a todos estos escenarios.

Algunas de las cosas que vamos a hacer son agregar una columna con cantidades absolutas y agrupar por "agregar una columna de balance de cantidad" y una tabla anidada usando la opción "todas las filas". También transformaremos esas tablas anidadas, ordenaremos el tipo de movimiento y la fecha de publicación, crearemos un total acumulado y solo mantendremos las filas según una condición.

Califiqué esto en un nivel intermedio porque la mayoría de las transformaciones que podemos hacer aquí son usando la interfaz de usuario. Dicho esto, pasemos a LuckyTemplates Power Query.

Tabla de contenido

• Agregar columna con cantidades absolutas en Power Query
• Agrupar por índice de ID y cantidad absoluta
• Transformación de tablas anidadas en LuckyTemplates Power Query
  • Tipo de movimiento de clasificación y fecha de contabilización
  • Crear un total acumulado
  • Filtrado de filas en función de una condición
• Conclusión

Agregar columna con cantidades absolutas en Power Query

Comenzaremos agregando una columna con cantidades absolutas. Seleccione la columna Cantidad , luego en la cinta o pestaña Agregar columna , vaya a Científica y luego haga clic en Valor absoluto .

Voy a cambiar el nombre de esta columna en la barra de fórmulas.

Agrupar por índice de ID y cantidad absoluta

Ahora podemos agrupar por índice de ID y cantidad absoluta. Seleccione Índice de ID , luego mantenga presionada la tecla shift o control para realizar una selección múltiple y luego haga clic en Abs qty (cantidad absoluta).

Encontrará Agrupar por en la pestaña Transformar.

Pero también puede encontrarlo en la pestaña Inicio.

Haga clic en él y ahora vamos a agregar una nueva columna. No es un “conteo”, pero va a ser la “suma” de la cantidad. Entonces, cambiémosle el nombre a Qty (cantidad) Balance . Va a ser una suma de la columna de cantidad. Y luego, agregaremos otra agregación, pero será especial, por lo que seleccionaremos Todas las filas . Démosle también un nombre a esta columna ( AllRows ). Luego, presione Aceptar.

Si hacemos clic en el espacio en blanco de la tabla, vemos una vista previa de la tabla anidada a continuación. Vemos que las cantidades de esta tabla anidada se cancelan entre sí y el saldo de cantidad es cero.

Si observamos la siguiente tabla anidada (haga clic en el lado del espacio en blanco de la siguiente fila), vemos que la columna de cantidad no se cancela entre sí y que el saldo de cantidad es mayor que cero.

Agreguemos una columna personalizada para que podamos usarla para nuestras transformaciones de tablas anidadas más adelante. Por lo tanto, seleccione Agregar columna personalizada .

Cambiemos el nombre de esa columna a Transformaciones y agregaremos cero (0) como marcador de posición. Luego, haga clic en Aceptar.

Transformación de tablas anidadas en LuckyTemplates Power Query

Ahora estamos listos para crear la lógica para transformar esas tablas anidadas. Pero no queremos escribir un montón de ellos, y no es necesario.

Podemos utilizar la interfaz de usuario en su mayor parte. Si construimos la lógica para esas transformaciones en una consulta separada, el escenario más elaborado que teníamos era el que tenía tres o cuatro filas.

Ahora, si hago clic en el lado derecho de la última fila, veo que esta tabla tiene cuatro filas. Entonces, podemos usar esto para construir nuestras transformaciones.

Haga clic con el botón derecho en el lado del espacio en blanco y seleccione Agregar como nueva consulta .

Vemos que la tabla anidada ahora se expandió y se agregó como una nueva consulta y podemos usar esto para construir la lógica que necesitamos.

Tipo de movimiento de clasificación y fecha de contabilización

Ahora, lo primero que debemos hacer es ordenar Tipo de movimiento . Elijamos descendente . De esta manera, las transacciones salientes siempre estarán en la parte superior.

A continuación, ordenaremos la Fecha de publicación como ascendente , asegurándonos de que si omitimos filas, siempre se hará de acuerdo con el principio FIFO (primero en entrar, primero en salir).

Crear un total acumulado

Para identificar las filas que vamos a eliminar, agregaré un total acumulado para deshabilitar y podemos usar List.FirstN para esa tarea. Esta función crea una lista basada en otra lista, donde los elementos principales se mantienen en función de un número específico o de un criterio.

Volvamos a nuestra consulta. Necesitamos dos cosas para List.FirstN. Primero, necesitamos esa lista, y esa lista es nuestra columna Cantidad . Si hago clic con el botón derecho en el encabezado de la columna Cantidad, obtenemos la opción Agregar como nueva consulta .

Y aquí vemos el código que necesitamos para generar esa lista. En el paso aplicado, puede ver que apunta al último paso y luego identifica la columna en esos corchetes.

La segunda parte que necesitamos para List.FirstN es un número para identificar cuántos números mantener de esa lista. Podemos usar el índice para eso, así que agregaré una columna de índice de uno (1) para esta fila.

Entonces, para el número 1, mantendrá el número en la primera fila de la columna Cantidad, y así sucesivamente. Y ahora podemos resumirlo.

Agreguemos una columna personalizada y llamémosla Total acumulado . Usamos List.FirstN aquí, y podemos ver que el primer parámetro fue esa lista, la columna Cantidad. Necesitamos apuntar al último paso, y ese último paso es el Índice Agregado .

Identificamos la columna que queremos, y esa es nuestra columna Cantidad. Nuestro recuento está en la columna Índice.

Ahora devuelve una lista. Si hacemos clic al lado en el espacio en blanco, podemos ver el contenido de esa lista. Para el primer registro, solo mantuvo la fila superior de esa lista. Para el segundo récord, mantuvo las dos filas superiores.

Ahora todo lo que tenemos que hacer es sumar estas cantidades y podemos usar Lists.Sum para hacerlo. Entonces, voy a agregar eso en la barra de fórmulas. Y también voy a poner Type .

Filtrado de filas en función de una condición

Con este total acumulado, podemos identificar las filas que queremos conservar. Solo queremos mantener las filas que son mayores que cero, así que agreguemos una condición de filtro.

Ahora podemos eliminar nuestras columnas auxiliares. Seleccione Índice y Total acumulado, luego haga clic en Quitar columnas.

En el Editor avanzado, ahora podemos copiar el código que hemos creado. Abriré el Editor avanzado y podemos ver que este es nuestro paso dividido. Entonces, podemos seleccionar y copiar todo lo que está debajo de eso.

Ahora volvamos a nuestra consulta original. Nuevamente, abra el Editor avanzado. Y en lugar del marcador de posición, pasemos a una nueva línea. Voy a usar la expresión " let " porque puede capturar valores de cálculos intermedios en variables. Entonces, "let" y luego ve a una nueva línea y pega mis transformaciones. También declararé Type .

El código resaltado arriba apunta a nuestra tabla anidada expandida, que se coloca dentro de la columna Todas las filas. Entonces, voy a señalar eso, reemplazando este código resaltado a AllRows .

Entonces nuestra columna de transformación ahora tiene tablas anidadas. Construimos nuestra consulta basándonos en la última fila, ¿verdad? Y eso contenía cuatro filas cuando comenzamos y solo contenía dos. También tenemos esas cantidades balanceadas con cero, ¿no? Y ahora tiene una mesa vacía.

Podemos eliminarlos filtrando en la columna Balance de cantidad. No queremos que el saldo de la cantidad sea igual a cero.

Y luego, seleccionaré mi columna de transformación y eliminaré otras columnas. Ahora puedo expandir mis tablas anidadas de transformación. No use el nombre de la columna original como prefijo y presione Aceptar.

En la pestaña Transformar, seleccione Tipo de datos y ya hemos terminado. Y así es como limpia los datos transaccionales.

Conceptos básicos de Unpivot y Pivot en LuckyTemplates – Editor de consultas Revise
mis consejos prácticos al usar el editor avanzado de LuckyTemplates
Transformaciones avanzadas en LuckyTemplates

Conclusión

En este tutorial, le mostré cómo limpiar datos transaccionales, específicamente eliminando filas duplicadas. Esta es una gran técnica que puede usar cuando utiliza LuckyTemplates Power Query.

Espero que hayas disfrutado este. Mire el video tutorial completo a continuación para obtener más detalles. Consulte los enlaces a continuación para obtener más contenido relacionado con LuckyTemplates Power Query Editor.

¡Salud!

Toronjil