Forum Discussion

aTChris's avatar
aTChris
Resolver I
5 years ago
Solved

Problemas con GroupKind.Local

Estoy trabajando con un conjunto de datos que tiene eventos registrados que necesito agrupar cuando cambia un atributo.

Usé https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

El problema que tengo es la fecha y hora de finalización y las filas siguientes Fecha y hora de inicio tiene un espacio.

Idealmente, necesito que el DateTime de finalización de la fila anterior sea 1 segundo menos que el DateTime de inicio.

Screenshot 2020-10-02 at 18.56.59.png

let
    Source = Excel.Workbook(File.Contents("C:/Data.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"moved", Int64.Type}, {"event_at", type datetime}, {"temp", type number}, {"co2", Int64.Type}, {"hum", type number}, {"mac", type text}, {"sn", type text}, {"message_type", Int64.Type}, {"building_id", Int64.Type}, {"timezone", type text}, {"floor_id", Int64.Type}, {"group_types", type text}, {"spaces", type text}, {"groups", type text}, {"space_types", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([message_type] = 2)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"sn"}, {{"Count", each _, type table [Column1=nullable number, moved=nullable number, event_at=nullable datetime, temp=nullable number, co2=nullable number, hum=nullable number, mac=nullable text, sn=nullable text, message_type=nullable number, building_id=nullable number, timezone=nullable text, floor_id=nullable number, group_types=nullable text, spaces=nullable text, groups=nullable text, space_types=nullable text]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"moved", "event_at", "message_type", "building_id", "floor_id", "group_types", "spaces", "groups", "space_types"}, {"Count.moved", "Count.event_at", "Count.message_type", "Count.building_id", "Count.floor_id", "Count.group_types", "Count.spaces", "Count.groups", "Count.space_types"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Count", {"sn", "Count.moved"}, {{"Start", each List.Min([Count.event_at]), type nullable datetime}, {"End", each List.Max([Count.event_at]), type nullable datetime}, {"Building.ID", each List.Min([Count.building_id]), type nullable number}, {"Spaces", each List.Min([Count.spaces]), type nullable text}, {"Groups", each List.Min([Count.groups]), type nullable text}, {"SpaceType", each List.Min([Count.space_types]), type nullable text}}, GroupKind.Local)
in
    #"Grouped Rows1"

¿Alguna idea?

  • Hola @aTChris ,

    Puede usar DAX para lograr esto.

    1. agregue una columna [Index].

    2. Cree una columna calculada.

    __start = 
    VAR x = 
    CALCULATE(
        MAX(Sheet6[end]),
        FILTER(
            Sheet6,
            Sheet6[Index] = EARLIER(Sheet6[Index]) - 1
        )
    )
    RETURN
    IF(
        x = BLANK(),
        [start],
        x + 1/86400
        
    )

    v-lionel-msft_0-1601889270523.png

    Saludos
    Lionel Chen

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

1 Reply

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support

    Hola @aTChris ,

    Puede usar DAX para lograr esto.

    1. agregue una columna [Index].

    2. Cree una columna calculada.

    __start = 
    VAR x = 
    CALCULATE(
        MAX(Sheet6[end]),
        FILTER(
            Sheet6,
            Sheet6[Index] = EARLIER(Sheet6[Index]) - 1
        )
    )
    RETURN
    IF(
        x = BLANK(),
        [start],
        x + 1/86400
        
    )

    v-lionel-msft_0-1601889270523.png

    Saludos
    Lionel Chen

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