Cómo calcular el porcentaje de Power Query del total o categoría

En esta publicación, veremos cómo calcular el porcentaje de un total en Power Query, pero también llevaremos esto un paso más allá para considerar cómo calcular el porcentaje de una categoría.

En Excel estándar, estos cálculos son simples porque estamos muy acostumbrados a las fórmulas para lograrlo. Para muchos, Power Query sigue siendo una herramienta más nueva y no funciona de la misma manera. Pero una vez que haya calculado un porcentaje de Power Query del total, creo que encontrará que es sencillo.

Tabla de contenido

• Porcentaje de Power Query del total: respuesta rápida
• Porcentaje del total en Power Query
• Porcentaje de categoría en Power Query
• Cómo funciona el % del total
• Cómo funciona el % de categoría
• Paso 1
• Paso 2
• Paso 3
• Etapa 4
• Paso 5
• Paso 6
• Paso 7
• Conclusión

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: 0010 Porcentaje del total en Power Query.xlsx

Tener acceso

Porcentaje de Power Query del total: respuesta rápida

Los datos de nuestro ejemplo se ven así; es una lista de ciudades donde una empresa tiene oficinas. Cada oficina tiene una región y un valor de plantilla.

Nuestro objetivo es:

• Calcule el % de la plantilla total en cada sitio
• Calcule el porcentaje de personal de la región en cada sitio

Porcentaje del total en Power Query

Calcular el porcentaje del total es razonablemente sencillo. Si bien la función SUMA no existe en Power Query, la función Lista.Suma sí.

1. Dentro de Power Query, haga clic en Agregar columna Columna personalizada
   
2. En el cuadro de diálogo Columna personalizada, ingrese la siguiente fórmula: =[Recuento de personal] / List.Sum(#”Tipo cambiado”[Recuento de personal])
3. Cambie la fórmula para que se ajuste a su escenario:
   • [Recuento] es el nombre de la columna para la que desea calcular el %
   • #”Tipo modificado” es el nombre del paso que se utilizará como fuente de la fórmula. Normalmente, este es el nombre del paso anterior en la ventana del Editor avanzado.
4. Asigne un nombre útil a la columna personalizada, como % del total , luego haga clic en Aceptar .
   
5. La columna % del total ahora se incluye en la ventana de vista previa.
   
6. Todavía no parece un porcentaje, así que cambie el tipo de datos haciendo clic en el botón ABC123 y seleccionando Porcentaje en el menú.
   

Eso es todo, aquí está la mesa final:

Eso no estuvo tan mal, ¿verdad? La solución fue muy similar a cómo lo haríamos en Excel. Continúe leyendo para comprender más sobre cómo funciona este cálculo.

Porcentaje de categoría en Power Query

Calcular el porcentaje de una categoría no es tan fácil. Power Query no tiene el equivalente de las funciones SUMAR.SI o SUMAR.SI, por lo que debemos pensar de manera diferente. En su lugar, creamos una fórmula de transformación para lograr el mismo resultado.

1. Dentro de Power Query, haga clic en Agregar columna Columna personalizada
   
2. En el cuadro de diálogo Columna personalizada , ingrese la siguiente fórmula:
   =[Recuento de personal] / Table.SelectRows( Table.Group(#”Tipo modificado”, {“Región”}, {{“Categoría total”, cada Lista.Sum([Recuento de personal) ]), escriba número}}), cada uno ([Región] = [Región])){[Región=[Región]]}[Categoría Total]
3. Cambie la fórmula para que se ajuste a su escenario:
   • #”Tipo modificado” es el nombre del paso que se utilizará como fuente de la fórmula. Normalmente, este es el nombre del paso anterior en la ventana del Editor avanzado.
   • [Región] o “Región” es el nombre de la columna que contiene el campo para categorizar
   • [Recuento] es el nombre de la columna para la que desea calcular el %
4. Asigne un nombre útil a la columna personalizada, como % de categoría , luego haga clic en Aceptar .
   
5. El % de la columna de categoría incluida en la ventana de vista previa.
6. Finalmente, cambie el tipo de datos a porcentaje.

Aquí está la mesa final:

El proceso fue similar a calcular el porcentaje de un total, pero la fórmula que pegaste es mucho más compleja. En las secciones siguientes, profundizaremos un poco más para comprender cómo funciona. Esto le permitirá crear esta transformación usted mismo.

Cómo funciona el % del total

Power Query en sí no tiene una fila total. Esto no es un problema, ya que la herramienta está diseñada para la manipulación de datos, en lugar de la presentación. Pero sí significa que no hay una fila total por la que dividir.

List.Sum es similar a la función SUMA de Excel. El siguiente código M usa esa fórmula para calcular el valor total de la columna Recuento, usando el paso #”Tipo cambiado” como fuente.

List.Sum(#"Changed Type"[Headcount])

Habiendo calculado el total, solo necesitamos dividir el número en cada fila de la columna de recuento por el resultado de la función List.Sum.

=[Headcount] / List.Sum(#"Changed Type"[Headcount])

Bastante fácil, ¿verdad?

Obtenga más información sobre la función List.Sum aquí: https://bioffthegrid.com/list-sum

Cómo funciona el % de categoría

Calcular el porcentaje de una categoría es un poco complicado. Hemos visto este escenario anteriormente cuando analizamos las funciones personalizadas . Si bien una función personalizada es una opción, también podemos lograr el resultado (y posiblemente más fácil) anidando transformaciones en una sola fórmula. .

Revisaremos las transformaciones una por una para que podamos comprender cómo funciona la técnica.

Paso 1

Comience con la tabla de origen cargada en Power Query.

Paso 2

Haga clic en Transformar grupo por en la cinta Power Query.

Se abre el cuadro de diálogo Agrupar por. Ingrese la siguiente informacion:

• Vista: Básica
• Columna: Región (es decir, la columna que contiene la columna de categoría)
• Nuevo nombre de columna: cualquier nombre que desee. Dado nuestro conjunto de datos, Región total parece sensata
• Operación: Suma – el cálculo que queremos realizar
• Columna: Recuento : la columna que contiene los números en los que queremos realizar la operación.

Haga clic en Aceptar para cerrar el cuadro de diálogo.

Mire la barra de fórmulas en la parte superior de la ventana de vista previa (haga clic en Ver barra de fórmulas si no está visible). El código M para este paso se ve así:

= Table.Group(#"Changed Type", {"Region"}, {{"Total Region", each List.Sum([Headcount]), type number}})

La función Table.Group es el código M que ejecuta la transformación Group By. Obtenga más información sobre la función Table.Group aquí: https://docs.microsoft.com/en-gb/powerquery-m/table-group

Paso 3

Filtre la columna de categoría para incluir un valor único. He seleccionado Gales . Luego haga clic en Aceptar.

El código M para este paso es:

= Table.SelectRows(#"Grouped Rows", each ([Region] = "Wales"))

La función Table.SelectRows es el código M que ejecuta un filtro de columna. Obtenga más información sobre la función Table.SelectRows aquí: https://docs.microsoft.com/en-gb/powerquery-m/table-selectrows

Etapa 4

Ahora combinemos las dos transformaciones del Paso 2 y del Paso 3 en una sola fórmula.

#”Filas agrupadas” es la referencia al paso anterior. Entonces, solo necesitamos hacer dos cambios simples:

• Agregue el texto antes del nombre del paso al comienzo del paso anterior.
• Agregue el texto después del nombre del paso al final del paso anterior.

El código M ahora se ve así:

=Table.SelectRows(Table.Group(#"Changed Type", {"Region"}, {{"Total Region", each List.Sum([Headcount]), type number}}), each ([Region] = "Wales"))

Las diferentes secciones son:

• La transformación del Paso 2
• El código agregado del Paso 3

Como el último paso se ha agregado al paso anterior, ya no es necesario, podemos eliminar el último paso.

La ventana de vista previa ahora solo tiene una línea de datos.

Paso 5

Haga clic derecho en el valor dentro de la columna Región total y seleccione Profundizar en el menú.

El código M para este paso se ve así:

= #"Grouped Rows"{[Region="Wales"]}[Total Region]

Paso 6

Ahora agreguemos el código del paso 5 al Paso 4.

El código M ahora se ve así:

=Table.SelectRows(Table.Group(#"Changed Type", {"Region"}, {{"Total Region", each List.Sum([Headcount]), type number}}), each ([Region] = "Wales")){[Region="Wales"]}[Total Region]

Las secciones son:

• La transformación del Paso 2
• El código agregado del Paso 3
• El código agregado del Paso 5

Como el desglose ahora se ha incorporado al paso anterior, podemos eliminar el último paso de la ventana de pasos aplicados.

Paso 7

Ahora tenemos todas las transformaciones necesarias, por lo que es hora de convertir la fórmula en su propia columna.

1. Copie todo el texto de la fórmula combinada de la barra de fórmulas.
2. Elimine el paso que contiene la fórmula de la lista de Pasos aplicados.
3. Haga clic en Agregar columna Columna personalizada
4. Pegue el texto copiado en el cuadro de diálogo Columna personalizada.
   
5. Reemplace cada instancia de "Gales" con [Región] . El código ahora se ve así: Obviamente, adaptará esto a su escenario.
   =Table.SelectRows( Table.Group(#”Tipo cambiado”, {“Región”}, {{“Región total”, cada List.Sum([Recuento]), escriba número}}), cada uno ([Región] = [Región])){[Región=[Región]]}[Total Región]
6. Finalmente, agregue la columna a dividir al inicio de la fórmula = [Headcount] / Table.SelectRows( Table.Group(#”Changed Type”, {“Región”}, {{“Total Region”, each List.Sum ([Recuento]), escriba número}}), cada uno ([Región] = [Región])){[Región=[Región]]}[Región total]

Eso es todo; ahora tenemos un % de la región para cada fila.

Conclusión

En esta publicación, nos enfocamos en cómo calcular el porcentaje de un total en Power Query y también el porcentaje de una categoría. A través de esto, aprendimos sobre la función List.Sum (que es similar a la función SUMA de Excel) y también cómo combine consultas en un solo paso de transformación. Esta es una gran técnica para lograr transformaciones más avanzadas.

Artículos Relacionados:

• Introducción a Power Query
• Cómo utilizar Power Query Group By para resumir datos
• Consulta de energía: total acumulado

---