Función agregada personalizada de Power M Query para devolver el valor más común dentro de un grupo

Un usuario Pregunto ✅

mikecrobp

¿Alguien puede ayudarme? Crear una función agregada personalizada para devolver el valor más común para una o más columnas para un conjunto de registros que tienen una agrupación única: es decir, que puede usar donde podría usar MAX, MIN, etc. pero el resultado es el más común valor que ocurre.

por ejemplo, si agrupa en la columna G1 y agrega en las columnas A1 y A2 con la primera tabla como datos de entrada, obtendrá la segunda tabla como salida (feliz de reformatear las tablas si alguien puede señalar cómo):

G1 A1 A2
un V1 V1
un V2 V1
un V2 V2
b V1 V1
b V2 V1

G1 A1 A2
un V2 V1
b V1 V1

Debe ser Power M Query, no DAX

Y supongo que la fórmula para poner en los parámetros Table.Group estaría bien en lugar de una función personalizada ordenada.

Algo que mira la lista de valores resultante, obtiene los valores distintos, luego cuenta las ocurrencias, ordena Hi-Lo y devuelve el primer valor. Pero no puedo entender eso.

Miguel

mikecrobp

En respuesta a mikecrobp

Aquí está la respuesta que se me ocurrió. Nunca he escrito una definición de función de Power M Query, por lo que todos los comentarios sobre el estilo y el rendimiento son bienvenidos.

Creo que es una función útil para cuando sus datos son en su mayoría correctos pero necesita tomar el valor común.

La lógica de la función es:

  1. obtener los valores únicos y convertirlos a la tabla
  2. encuentre el número de veces que cada uno ocurre en la entrada
  3. Ordene por orden descendente y devuelva la cadena en la primera fila, la cadena que ocurre con más frecuencia

Podría agregar algo sobre cómo se manejan los nulos (por ejemplo, ignorar nulo si la cadena superior y devolver la siguiente hacia abajo) pero esto parece funcionar para mí

La función es la más común (debe nombrar la consulta)

let 
fnMostCommon = (ListIn)  =>
let
uniquevalues=List.Distinct(ListIn),
result=Table.FromList(uniquevalues,null,{"u"}),
result2=Table.AddColumn(result ,"freq", each List.Count(List.PositionOf(ListIn, [u], 100))),
result3 = Table.Sort(result2,{"freq", Order.Descending}),
result4 = List.First(result3[u])
in result4
in fnMostCommon

Y el arnés de prueba es:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQozhBCxOlC+ETa+EZifhKY+CVl9LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [G1 = _t, A1 = _t, A2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"G1", type text}, {"A1", type text}, {"A2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"G1"}, {{"Agg", each MostCommon([A1]), type text}, {"Agg2", each AllConcat([A1]), type text}}),
    a = #"Grouped Rows"{[G1="a"]}[Agg]
in
    a

Hola @mikecrobp,

Creé una muestra. Quizás ayude un poco. Por favor inténtelo.

  • Duplique la tabla original como Tabla (2) y agrúpela por ella.

5.PNG

  • Agrupar por columnas de la tabla.

1.PNG

  • Fusionar dos tablas y agregar la nueva columna

2.PNG

  • Elimina la columna y las filas «Recuento».

4.PNG

Tabla:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQozhBCxOlC+ETa+EZifhKY+CVl9LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [G1 = _t, A1 = _t, A2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"G1", type text}, {"A1", type text}, {"A2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"G1", "A1"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"G1"}, #"Table (2)", {"G1"}, "Table (2)", JoinKind.LeftOuter),
    #"Aggregated Table (2)" = Table.AggregateTableColumn(#"Merged Queries", "Table (2)", {{"Count", List.Min, "Min of Table (2).Count"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Aggregated Table (2)",{"Count"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",1)
in
    #"Removed Bottom Rows"

Tabla 2):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQozhBCxOlC+ETa+EZifhKY+CVl9LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [G1 = _t, A1 = _t, A2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"G1", type text}, {"A1", type text}, {"A2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"G1", "A1"}, {{"Count", each List.Min([A2]), type text}})
in
    #"Grouped Rows"

Atentamente,

Xue Ding

Si esta publicación ayuda, entonces por favor considere Acéptalo como la solución para ayudar a los demás miembros a encontrarlo más rápidamente. Prestigio son agradables también.

mikecrobp

En respuesta a v-xuding-msft

Gracias Xue Ding. por supuesto, así es como debería haber presentado el problema, ¡con código usando Table.FromRows!

Mis disculpas, debería haber dicho que he estado usando la técnica de crear una nueva tabla y volver a fusionarla.

Basado en una publicación en el desbordamiento de la pila: seleccione una fila con el valor MÁXIMO por categoría Power BI

Quería crear una nueva función agregada porque:

1 – será fácil de leer en el código. Aparecerá simplemente con las funciones agregadas estándar como MAX, SUM, etc.

2 – Para ver si corría más rápido

3 – reducirá el número de consultas, para mayor claridad

Escribir la pregunta ayer me ayudó a pensar en el problema y escribiré algo basado en los datos de tu prueba. Mike

mikecrobp

En respuesta a mikecrobp

Aquí está la respuesta que se me ocurrió. Nunca he escrito una definición de función de Power M Query, por lo que todos los comentarios sobre el estilo y el rendimiento son bienvenidos.

Creo que es una función útil para cuando sus datos son en su mayoría correctos pero necesita tomar el valor común.

La lógica de la función es:

  1. obtener los valores únicos y convertirlos a la tabla
  2. encuentre el número de veces que cada uno ocurre en la entrada
  3. Ordene por orden descendente y devuelva la cadena en la primera fila, la cadena que aparece con más frecuencia

Podría agregar algo sobre cómo se manejan los nulos (por ejemplo, ignorar nulo si la cadena superior y devolver la siguiente hacia abajo) pero esto parece funcionar para mí

La función es la más común (debe nombrar la consulta)

let 
fnMostCommon = (ListIn)  =>
let
uniquevalues=List.Distinct(ListIn),
result=Table.FromList(uniquevalues,null,{"u"}),
result2=Table.AddColumn(result ,"freq", each List.Count(List.PositionOf(ListIn, [u], 100))),
result3 = Table.Sort(result2,{"freq", Order.Descending}),
result4 = List.First(result3[u])
in result4
in fnMostCommon

Y el arnés de prueba es:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQozhBCxOlC+ETa+EZifhKY+CVl9LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [G1 = _t, A1 = _t, A2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"G1", type text}, {"A1", type text}, {"A2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"G1"}, {{"Agg", each MostCommon([A1]), type text}, {"Agg2", each AllConcat([A1]), type text}}),
    a = #"Grouped Rows"{[G1="a"]}[Agg]
in
    a

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *