Forum Discussion
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.
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 )Saludos
Lionel ChenSi 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-msftCommunity 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 )Saludos
Lionel ChenSi este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.