En este escenario partimos de una tabla Sales que contiene en el campo Amount la cifra de ventas y en el campo Customer Id una referencia al cliente asociado.
El objetivo es obtener una función de consulta que reciba un número n como argumento y devuelva una tabla conteniendo los n clientes con mayor facturación, ordenada de mayor a menor facturación. Esta tabla deberá tener dos columnas con información del identificador del cliente y la suma de sus compras.
Para empezar a trabajar en la consulta, supongamos que queremos extraer 5 clientes. El primer paso consiste en calcular, para cada cliente, sus ventas (posteriormente ordenaremos esta tabla y seleccionaremos el número de filas que nos interesa).
Para esto, el lenguaje M nos ofrece la función Table.Group, que agrega una tabla según un campo, y añade columnas calculadas adicionales obtenidas tras realizar esta agregación.
En nuestro caso queremos agrupar la tabla de ventas según el campo Customer Id y queremos añadir una nueva columna (a la que podemos llamar Total Sales) que contenga la suma de ventas de cada cliente identificado.
La función Table.Group requiere tres argumentos principales: la tabla a agregar (Sales), el campo (o los campos) según los cuales se va a realizar la agregación (Customer Id) y una lista conteniendo información sobre las columnas a crear: para cada una habrá que añadir una lista con el nombre de la columna y la función que va a determinar los valores de la misma (es decir, su función generadora). Nosotros solo queremos crear una columna, de forma que, parcialmente en pseudo-código, nuestra fórmula de paso sería la siguiente:
Sales,
"Customer Id",
{
{"Total Sales", función-generadora, type number}
}
)
Nuestra función generadora deberá sumar los valores de la columna Amount (recordemos que, cuando se aplique nuestra función generadora, la tabla va a estar filtrada según el valor de Customer Id que se esté considerando). Para realizar esta suma podemos usar la función List.Sum, lo que significa que nuestra función generadora puede ser la siguiente:
Nuestra consulta quedaría, por lo tanto, del siguiente modo:
numberOfCustomers = 5,
GroupedRows = Table.Group(
Sales,
"Customer Id",
{
{"Total Sales", each List.Sum([Amount]), type number}
}
)
in
GroupedRows
Obsérvese que en el bloque que especifica la creación de la columna Total Sales se ha añadido como tercer argumento el tipo de la columna a crear:
Ahora viene la parte más sencilla: ordenar y seleccionar las primeras filas. Para ordenar la tabla vamos a usar la función Table.Sort. Esta función recibe una tabla y los criterios de ordenación, devolviendo la tabla ordenada. En nuestro escenario el criterio de ordenación viene determinado por el contenido de la columna Total Sales, y queremos que la ordenación sea descendente, lo que quiere decir que nuestra función de paso podría ser la siguiente:
GroupedRows,
{
{"Total Sales", Order.Descending}
}
)
Vemos en la imagen anterior que los clientes ya se muestran ordenados según su facturación.
Para quedarnos con las primeras numberOfCustomers filas, no tenemos más que recurrir a la función Table.FirstN, con lo que nuestra consulta quedaría así:
numberOfCustomers = 5,
GroupedRows = Table.Group(
Sales,
"Customer Id",
{
{"Total Sales", each List.Sum([Amount]), type number}
}
),
SortedRows = Table.Sort(
GroupedRows,
{
{"Total Sales", Order.Descending}
}
),
TopNRows = Table.FirstN(SortedRows, numberOfCustomers)
in
TopNRows
Ahora, para convertir nuestra consulta en una función de consulta, añadimos la cabecera y eliminamos la fórmula de paso que asigna el valor 5 a la variable numberOfCustomers:
let
GroupedRows = Table.Group(
Sales,
"Customer Id",
{
{"Total Sales", each List.Sum([Amount]), type number}
}
),
SortedRows = Table.Sort(
GroupedRows,
{
{"Total Sales", Order.Descending}
}
),
TopNRows = Table.FirstN(SortedRows, numberOfCustomers)
in
TopNRows
Le damos un nombre apropiado (TopCustomers, por ejemplo) y la invocamos para ver el resultado:
(en la imagen anterior se muestra el resultado de pasar a la función el número 3 como argumento)
