Encontrar diferencia, media y desarrollo estándar de valores separados por comas

[Syndicate_Admin]

Querida comunidad,

Estoy tratando de encontrar la diferencia entre las entidades distintas de cero de la columna 'Salida' (que es un valor vectorial de tamaño 8 y está en formato separado por comas). También me gustaría calcular la media y la desviación estándar para la columna 'Diferencia' resultante si el tamaño es mayor que 1.

Salida	Diferencia	Significar	Std Dev.
37,28,4,0,0,0,0,0	9,24	16.5	10.61
54,50,33,2,1,0,0,0	4,17,31,1	13.25	13.72
0,0,0,0,0,0,0,0	0	0	0.00
2884,0,0,0,0,0,0,0	0	0	0
500,450,420,385,370,300,150,50	50,30,35,15,70,150,100	64.28	47.12

P. ej..

• [37,28,4,0,0,0,0,0,0] -> [(37-28), (28-4)] -> [9, 24]. Omita calcular la diferencia si el valor es cero.
• [54,50,33,2,1,0,0,0] -> [(54-50), (50-33), (33-2), (2-1)] -> [4,17,31,1]

Realmente agradecería su ayuda en esto.

¡Gracias de antemano!

[Syndicate_Admin]

@DreamToGet ,

Por favor, intente:

Primero agregue el índice a la tabla:

Luego duplique su tabla original y divídala en filas y agregue índice

Aquí está el código M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5rCgMhDISvUvw9hDyNPcvi/a/RuLZLH9REhoxfGI+jWUIHHPyqhnaHeol0iiVMXdrE0cIRDDMo5IIdkjCBLNRIY2vqucL4qHq7LjGfiI7h/6kdzAyvaNeKHwHL0vKkvODb96m99c/qKAS5QeEfssDupOPd8CTR5zTnAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Output = _t, Difference = _t, #"Mean " = _t, #"Std Dev." = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Output", type text}, {"Difference", type text}, {"Mean ", type number}, {"Std Dev.", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Difference", "Mean ", "Std Dev."}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Output", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Output"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Output", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Salida:

A continuación, agregue la columna:

Difference =
VAR _a =
    FILTER (
        'Table (2)',
        'Table (2)'[Index] >= ( 'Table'[Index] * 8 - 7 )
            && 'Table (2)'[Index] <= 'Table'[Index] * 8
    )
VAR _b =
    ADDCOLUMNS (
        _a,
        "Diff",
            IF (
                MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) = 0,
                0,
                [Output]
                    - MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] )
            )
    )
VAR _c =
    CONCATENATEX ( FILTER ( _b, [Diff] <> 0 ), [Diff], "," )
RETURN
    IF ( MAXX ( _b, [Diff] ) = 0, "0", _c )


Mean =
VAR _a =
    FILTER (
        'Table (2)',
        'Table (2)'[Index] >= ( 'Table'[Index] * 8 - 7 )
            && 'Table (2)'[Index] <= 'Table'[Index] * 8
    )
VAR _b =
    ADDCOLUMNS (
        _a,
        "Diff",
            IF (
                MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) = 0,
                0,
                [Output]
                    - MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] )
            )
    )
VAR _c =
    AVERAGEX ( FILTER ( _b, [Diff] <> 0 ), [Diff] )
RETURN
    IF ( ISBLANK ( _c ), 0, _c )


Std.DEV =
VAR _a =
    FILTER (
        'Table (2)',
        'Table (2)'[Index] >= ( 'Table'[Index] * 8 - 7 )
            && 'Table (2)'[Index] <= 'Table'[Index] * 8
    )
VAR _b =
    ADDCOLUMNS (
        _a,
        "Diff",
            IF (
                MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] ) = 0,
                0,
                [Output]
                    - MAXX ( FILTER ( _a, [Index] = EARLIER ( 'Table (2)'[Index] ) + 1 ), [Output] )
            )
    )
VAR _c =
    STDEVX.S ( FILTER ( _b, [Diff] <> 0 ), [Diff] )
RETURN
    IF ( ISBLANK ( _c ), 0, _c )

Resultado final:

Saludos

Jianbo Li

Si esta publicación ayuda, considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.