Consulta de energía: total acumulado
En esta publicación, quiero cubrir un requisito de generación de informes común, que es un total acumulado de Power Query. Anteriormente cubrí un tema similar en una publicación sobre totales acumulados en tablas ; sin embargo, esos métodos no funcionan en Power Query. Por lo tanto, necesitaremos encontrar un enfoque alternativo.
Tanto las tablas como Power Query se diseñaron con contexto de fila. Básicamente, esto significa que al utilizar sistemas de referencia estructurados, una fórmula solo puede recuperar valores de una columna en la misma fila; (es decir, no existe una forma sencilla de referirse a las filas superiores o inferiores). Con las tablas, podemos resolver el problema del total acumulado utilizando fórmulas que devuelven rangos como ÍNDICE o COMPENSACIÓN. Sin embargo, Power Query no tiene estas funciones ni siquiera equivalentes. Por lo tanto, para resolver este problema de Power Query, recurriremos a las funciones de lista de códigos M.
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre de archivo: 0008 Power Query ejecutando total.xlsx
Tener acceso
Respuesta corta
En las secciones siguientes, veremos cómo funcionan las dos funciones de lista. Pero si desea una respuesta rápida, siga los pasos a continuación.
Aquí están nuestros datos de ejemplo, ya cargados en Power Query.
El objetivo es crear un número total acumulado de regalos recibidos/dados el último día de los 12 días de Navidad (basado en la canción tradicional ).
- Agregue una columna de índice comenzando en 1 haciendo clic en Agregar columna – Columna de índice (menú desplegable) – Desde 1
- A continuación, haga clic en Agregar columna – Columna personalizada
- En el cuadro de diálogo Columna personalizada, ingrese la siguiente fórmula:
=List.Sum(List.FirstN(#"Índice agregado"[Número],[Índice]))
- Asigne un nombre útil a la columna personalizada, como Total acumulado , luego haga clic en Aceptar .
- El total acumulado ahora se incluirá en la ventana de vista previa. Ahora eres libre de eliminar la columna Índice si lo deseas.
Para su escenario, es posible que deba cambiar lo siguiente:
- Nombre de la columna que se utilizará en el total acumulado: en el ejemplo anterior es la columna [Número], pero será diferente según su escenario.
- Nombre del paso que crea la columna Índice; en el ejemplo anterior, es el paso # “Índice agregado”. Busque el código M en el Editor avanzado si no está seguro del nombre del paso.
- Nombre de la columna Índice: en el ejemplo anterior se llama [Índice]
Podrías irte ahora y estar feliz de haber descubierto cómo realizar un total acumulado de Power Query. ¿Pero no sería mejor entender cómo funciona esto? ¿Por qué no te quedas unos minutos mientras te lo cuento :-).
Funciones de lista
Las funciones de lista son un grupo de funciones de código de Power Query M que realizan transformaciones en listas. Las listas pueden ser (a) una sola columna de datos de una tabla o (b) una lista de constantes, como {1,2,3} o {“cat”, “sat”, “mat”}
Para crear el total acumulado, utilizamos dos funciones de lista juntas:
- Lista.Suma ( https://docs.microsoft.com/en-gb/powerquery-m/list-sum )
- Lista.FirstN ( https://docs.microsoft.com/en-gb/powerquery-m/list-firstn )
Veamos cada una de estas funciones con más detalle.
Lista.PrimeraN
La función List.FirstN crea una lista basada en los primeros elementos de otra lista. Los primeros elementos se pueden definir como un número específico de elementos o los primeros en cumplir una condición.
El formato de la función es el siguiente:
=List.FirstN(lista como lista, countOrCondition como cualquiera) como cualquiera
Ejemplo básico n.° 1:
En este ejemplo, tenemos una lista de 5 constantes y se devuelven los primeros 3 elementos.
=Lista.PrimeroN ({1,2,3,4,5}, 3)
- lista = {1,2,3,4,5}
- cuentaOCondición = 3
El resultado de la fórmula anterior sería {1,2,3} .
Ejemplo básico n.° 2:
=Lista.PrimeraN({1,2,3,4,5,3,2,1}, cada uno _ =3)
- lista = {1,2,3,4,5,4,3,2,1}
- countOrCondition = cada _ =3
El resultado de la fórmula anterior sería {1,2,3}
Se podría pensar que devolvería {1,2,3,2,1}, ya que todos son menores o iguales a 3. Sin embargo, como esta es la función List.FirstN, solo devuelve los resultados hasta que la condición sea falsa. y no devuelve más resultados.
Lista.Suma
La función List.Sum calcula la suma de los valores de la lista.
El formato de la función es:
=List.Sum(lista como lista, precisión opcional como número que acepta valores NULL)
Ejemplo básico n.° 3:
En este ejemplo, hay una lista de 3 constantes, que se sumarán.
=Lista.Suma({1,2,3})
- lista = {1,2,3}
El resultado de la fórmula anterior sería 6 .
Anidando List.FirstN en List.Sum
Al igual que las funciones estándar de Excel, con Power Query podemos anidar una función dentro de otra.
Ejemplo básico n.° 4:
Combinando el ejemplo n.° 1 y el ejemplo n.° 3, obtenemos la siguiente fórmula:
=Lista.Suma(Lista.PrimeroN ({1,2,3,4,5}, 3))
El resultado de la fórmula anterior es 6 . Si no está seguro de cómo funciona esto, vuelva a leer esta sección; es un punto crucial.
Si desea obtener más funciones de Power Query, debe consultar la guía de referencia de Microsoft aquí: https://docs.microsoft.com/en-gb/powerquery-m/power-query-m-function-reference .
Cómo funciona
Ahora entendemos cómo funcionan las funciones en un nivel básico; Es hora de volver a nuestro ejemplo de total acumulado original.
Los datos de origen, después de crear la columna Índice, tienen este aspecto:
Primero, necesitamos crear una lista con los elementos correctos. La fórmula para hacerlo es esta:
=List.FirstN(#"Índice agregado"[Número],[Índice])
- list = #”Índice agregado”[Número]
Esto realizará el paso llamado #”Índice agregado” y usará la columna llamada Número como base de la lista. Si no está seguro del nombre del código M para el paso, haga clic en Ver – Editor avanzado . El nombre del paso es la parte que necesitamos (ver resaltado a continuación).
Esta parte de la fórmula nos da una lista de los valores de la columna Número de la tabla de datos {12,11,10,9,8,7,6,5,4,3,2,1} - countOrCondition = [Índice]
La primera fila tiene un índice de 1. Por lo tanto, la función List.FirstN devolverá el primer resultado de la lista, que es {12}
La segunda fila es un índice de 2, etc. Por lo tanto, la función devolverá el primeros dos elementos, {12,11}
La tercera fila tiene un índice de 3. Por lo tanto, la función devolverá los primeros 3 elementos, {12,11,10}
Esto continúa para cada fila de la tabla
Luego, anidamos la función dentro de la función List.Sum:
=List.Sum(List.FirstN(#"Índice agregado"[Número],[Índice]))
Para la primera fila el resultado es 12, ya que el cálculo sería:
- Lista.Suma({12})
La segunda fila, el resultado es 23, como sería el cálculo:
- Lista.Suma({12,11})
La tercera fila, el resultado es 33, como quedaría el cálculo:
- Lista.Suma({12,11,10})
El cálculo se implementa para cada fila de la tabla. Al hacer esto, hemos creado una columna de total acumulado en Power Query.
Conclusión
Las funciones de lista son muy poderosas. Para los usuarios de Excel, pueden resultar difíciles de entender, ya que la fórmula debe funcionar para cada fila de la tabla, en lugar de para una sola celda.
Deja una respuesta