Columna calculada: número de identificación de la caja

Un usuario Pregunto ✅

OriK

Hola, estoy tratando de crear 2 columnas calculadas:

1) ID de Sprint

2) Sprint Time: comienza desde 0 hasta que la velocidad sea menor a 15

Probé esta fórmula pero no tuve suerte …

Sprint ID =
SI (Datos[Helper]= 1,
CALCULAR (COUNTROWS (Datos),
FILTRO (Datos,
Datos[Index] <= ANTES (Datos[Index]) && Datos[Date]= ANTES (Datos[Date]) )) ,BLANCO())
Fecha Tiempo Segundos Velocidad Aceleración Índice Sprint Time Sprint ID
02/03/2019 18:19:00 3 13.51 0,01 0
02/03/2019 18:19:00 3 13.51 0,01 1
02/03/2019 18:19:00 3 13,74 0,17 2
02/03/2019 18:19:00 4 14.06 0,35 3
02/03/2019 18:19:00 4 13,63 0,07 4
02/03/2019 18:19:00 4 13,84 0,2 5
02/03/2019 18:19:00 4 13,98 0,24 6
02/03/2019 18:19:00 4 14.36 0,45 7
02/03/2019 18:19:00 4 14,82 0,65 8
02/03/2019 18:19:00 4 15.16 0,73 9 0 1
02/03/2019 18:19:00 4 15.39 0,71 10 1 1
02/03/2019 18:19:00 5 15.46 0,58 11 2 1
02/03/2019 18:19:00 5 15.46 0,58 12 3 1
02/03/2019 18:19:00 5 15,81 0,67 13 4 1
02/03/2019 18:19:00 5 16.04 0,67 14 5 1
02/03/2019 18:19:00 5 16.29 0,68 15 6 1
02/03/2019 18:20:00 5 17.1 0,87 dieciséis 7 1
02/03/2019 18:20:00 5 17.1 0,87 17 8 1
02/03/2019 18:20:00 5 17.06 0,62 18 9 1
02/03/2019 18:20:00 5 17,49 0,77 19 10 1
02/03/2019 18:20:00 5 17,69 0,71 20 11 1
02/03/2019 18:20:00 6 17,67 0,52 21 12 1
02/03/2019 18:20:00 6 17,62 0,36 22 13 1
02/03/2019 18:20:00 6 17.54 0,21 23 14 1
02/03/2019 18:20:00 6 17,73 0,29 24 15 1
02/03/2019 18:20:00 6 17,44 0,02 25 dieciséis 1
02/03/2019 18:20:00 6 17.52 0,08 26 17 1
02/03/2019 18:20:00 6 17.45 0,01 27 18 1
02/03/2019 18:20:00 11 10.16 0,56 28
02/03/2019 18:20:00 12 10.15 0,32 29
02/03/2019 18:20:00 12 10.29 0,34 30
02/03/2019 18:20:00 12 10,8 0,61 31
02/03/2019 18:20:00 12 10,56 0,29 32
02/03/2019 18:20:00 12 10.18 0,01 33
02/03/2019 18:20:00 12 10,7 0,36 34
02/03/2019 18:20:00 13 10,79 0,34 35
02/03/2019 18:20:00 13 14,71 2,07 99
02/03/2019 18:20:00 13 15,78 2.3 100 0 2
02/03/2019 18:20:00 13 16.39 2.14 101 1 2
02/03/2019 18:20:00 13 17.58 1,91 102 2 2
02/03/2019 18:20:00 13 17.58 1,91 103 3 2
02/03/2019 18:20:00 13 17,86 1,62 104 4 2
02/03/2019 18:20:00 13 18.56 1,7 105 5 2
02/03/2019 18:20:00 14 19.14 1,68 106 6 2
02/03/2019 18:20:00 14 19.45 1,48 107 7 2
02/03/2019 18:20:00 14 20.08 1,54 108 8 2
02/03/2019 18:20:00 14 20.19 1,24 109 9 2
02/03/2019 18:20:00 14 20,77 1,33 110 10 2
02/03/2019 18:20:00 14 21.07 1.2 111 11 2

ImkeF

En respuesta a BekahLoSurdo

Hola @BekahLoSurdo,

esa es una buena solución al problema.

Sin embargo, si lo aplica a datos grandes, podría volverse lento. Esto probablemente se ejecutará mucho más rápido y también será un poco más corto:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZZdlsIwCEb30mdPhJC/uhWP+9/GNHxEnZ4M6bxYq70mcIH4fG4U7yT3SLxvt43bg/cH0fFW+q2EzMeVAulle93+BfAVoCYFuB6X6AL9QU6BigKS9TeWgIQi2FLVjy4ADVuKx2u+8vze8Hy/LVdCEISQegj1CtCiAqUDbQ3kwFih9tD3K4DsANSbbzqDSFgi9+DZVz0jfNdGNFRTqZroNVECpS/Ct21ERORFdzX3HemLqAGbarrE3LcDzH2fAKvxEvXbK0Qyf7rG3PiJKF/G49z4IIoRFf76ruLc+JlA3Wq9x7nxE5Gt93RXc+MnoqK9VWOcGz8RCWuQxuEbH7tCHNRrJPrKxxp5e8/B6Dtn9Nzo2azJ8qVzNASriEbiWx+Ilbv0HIivfSAYb6VvU3ztg8jl40R87+9Q2idh4osfSP0Ul/jiWYz4Dt43DyRZf+D02BcpFkytqmUSsOYixYIhpOM3BtZpTIskixWlTqywo3wWWZ4yizQb07SkbRjRlUQ3FACrIaZFojXm3WK3OUx+kw1Gu4xDArNos6SDDk3MGDRMiz4zBp/jhGdaVIExGMVBNCN/HKm/GUaVsf7z4H6mvn4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Seconds = _t, Velocity = _t, Acceleration = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Seconds", Int64.Type}, {"Velocity", type number}, {"Acceleration", type number}, {"Index", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "GreaterThan15", each [Velocity] > 15),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"GreaterThan15"}, {{"All", each if List.First(_[GreaterThan15]) = true then Table.AddIndexColumn(_, "SprintTime",0,1) else _}}, GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index1", 0, 1),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Index", "All", {"Date", "Time", "Seconds", "Velocity", "Acceleration", "Index", "SprintTime"}, {"Date", "Time", "Seconds", "Velocity", "Acceleration", "Index", "SprintTime"}),
    AddSprintID = Table.AddColumn(#"Expanded All", "SprintID", each if [GreaterThan15] then Number.RoundUp([Index1]/2) else null),
    #"Removed Columns" = Table.RemoveColumns(AddSprintID,{"GreaterThan15", "Index1"})
in
    #"Removed Columns"

La clave está en el cuarto parámetro de la función Table.Group, donde usa GroupKind.Local. Vea este artículo sobre cómo funciona: https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

vanessafvg

@OriK, no siento que hayas explicado esto claramente, ya tienes una identificación de sprint, ¿qué identificación de sprint estás tratando de calcular?

tiempo de sprint? ¿Quieres sumar todo el tiempo hasta que la velocidad <15?

también en tu cálculo tienes ayudante? Datos[Helper]= 1

¿Dónde está eso en estos datos?

OriK

En respuesta a vanessafvg

Hola, lo siento, no estaba claro, la tabla que adjunté fue mi resultado de dasire, lo que tengo en este momento es

Fecha Hora Segundos Velocidad Aceleración

Fecha Tiempo Segundos Velocidad Aceleración
02/03/2019 18:19:00 3 13.51 0,01
02/03/2019 18:19:00 3 13.51 0,01
02/03/2019 18:19:00 3 13,74 0,17
02/03/2019 18:19:00 4 14.06 0,35
02/03/2019 18:19:00 4 13,63 0,07
02/03/2019 18:19:00 4 13,84 0,2
02/03/2019 18:19:00 4 13,98 0,24
02/03/2019 18:19:00 4 14.36 0,45
02/03/2019 18:19:00 4 14,82 0,65
02/03/2019 18:19:00 4 15.16 0,73
02/03/2019 18:19:00 4 15.39 0,71
02/03/2019 18:19:00 5 15.46 0,58
02/03/2019 18:19:00 5 15.46 0,58
02/03/2019 18:19:00 5 15,81 0,67
02/03/2019 18:19:00 5 16.04 0,67
02/03/2019 18:19:00 5 16.29 0,68
02/03/2019 18:20:00 5 17.1 0,87
02/03/2019 18:20:00 5 17.1 0,87
02/03/2019 18:20:00 5 17.06 0,62
02/03/2019 18:20:00 5 17,49 0,77
02/03/2019 18:20:00 5 17,69 0,71
02/03/2019 18:20:00 6 17,67 0,52
02/03/2019 18:20:00 6 17,62 0,36
02/03/2019 18:20:00 6 17.54 0,21
02/03/2019 18:20:00 6 17,73 0,29
02/03/2019 18:20:00 6 17,44 0,02
02/03/2019 18:20:00 6 17.52 0,08
02/03/2019 18:20:00 6 17.45 0,01
02/03/2019 18:20:00 11 10.16 0,56
02/03/2019 18:20:00 12 10.15 0,32
02/03/2019 18:20:00 12 10.29 0,34
02/03/2019 18:20:00 12 10,8 0,61

BekahLoSurdo

En respuesta a OriK

Hola @OriK,

Haría esto en Power Query. Usando sus datos iniciales en un libro de Excel, aquí está mi código:

let
    Source = Excel.Workbook(File.Contents("C:UsersrlosurdoDesktopSprintTime.xlsx"), null, true),
    Original_Sheet = Source{[Item="Original",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Original_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Time", type datetime}, {"Seconds", Int64.Type}, {"Velocity", type number}, {"Acceleration", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    Velocities = Table.FromColumns(
	Table.ToColumns(#"Added Index") & 
	Table.ToColumns(
		Table.InsertRows(Table.SelectColumns(#"Added Index","Velocity"),0,{[Velocity = null]})) &
	Table.ToColumns(
		Table.RemoveFirstN(Table.SelectColumns(#"Added Index","Velocity"),1)),
	Table.ColumnNames(#"Added Index") & {"PreviousVelocity", "NextVelocity"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(Velocities,1),
    #"Added SprintAudit" = Table.AddColumn(#"Removed Bottom Rows", "SprintAudit", each if [Velocity] >= 15 then if [PreviousVelocity] < 15 then "Initial" else if [NextVelocity] < 15 or [NextVelocity] is null then "Completion" else "Continued" else null),
    #"Added Join Key 1" = Table.AddColumn(#"Added SprintAudit", "Join Key", each if [SprintAudit] <> null then 1 else 0),
    InitialIndexTable = Table.SelectRows(#"Added Join Key 1", each ([SprintAudit] = "Initial")),
    #"Added Initial Index" = Table.AddIndexColumn(InitialIndexTable, "Sprint Index", 1, 1),
    CompletionIndexTable = Table.SelectRows(#"Added Join Key 1", each ([SprintAudit] = "Completion")),
    #"Added Completion Index" = Table.AddIndexColumn(CompletionIndexTable, "Sprint Index", 1, 1),
    IndexTable = Table.Combine({#"Added Completion Index", #"Added Initial Index"}),
    #"Removed Other Columns" = Table.SelectColumns(IndexTable,{"SprintAudit", "Sprint Index", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[SprintAudit]), "SprintAudit", "Index", List.Sum),
    #"Added Join Key" = Table.AddColumn(#"Pivoted Column", "Join Key", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Join Key 1",{"Join Key"},#"Added Join Key",{"Join Key"},"IndexTable",JoinKind.LeftOuter),
    #"Expanded IndexTable" = Table.ExpandTableColumn(#"Merged Queries", "IndexTable", {"Sprint Index", "Completion", "Initial"}, {"Sprint Index", "Completion", "Initial"}),
    #"Added Sprint ID" = Table.AddColumn(#"Expanded IndexTable", "Sprint ID", each try if [Index] >= [Initial] and [Index] <= [Completion] then [Sprint Index] else 0 otherwise null),
    #"Filtered Rows" = Table.SelectRows(#"Added Sprint ID", each ([Sprint ID] <> 0)),
    Partition = Table.Group(#"Filtered Rows", {"Sprint ID"}, {{"Partition", each Table.AddIndexColumn(_, "Sprint Time Setup",0,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Time", "Seconds", "Velocity", "Acceleration", "Index", "Sprint Time Setup"}, {"Date", "Time", "Seconds", "Velocity", "Acceleration", "Index", "Sprint Time Setup"}),
    #"Added Sprint Time" = Table.AddColumn(#"Expanded Partition", "Sprint Time", each if [Sprint ID] <> null then [Sprint Time Setup] else null),
    #"Sorted Rows" = Table.Sort(#"Added Sprint Time",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Sprint Time Setup"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Time", "Seconds", "Velocity", "Acceleration", "Index", "Sprint Time", "Sprint ID"})
in
    #"Reordered Columns"

Aquí está la tabla resultante en Power BI:

Sprint.PNG

Básicamente, creé algunas tablas temporales para poder definir e indexar cada sprint como su propia entidad. Luego usé la magia de partición de @ ImkeF (https://community.powerbi.com/t5/Desktop/Custom-column-Index-or-Ranking-by-other-column/td-p/33864) para agregar un índice para cada ID de Sprint individual.

Avíseme si tiene alguna pregunta o si desea que le envíe mi libro de trabajo.

ImkeF

En respuesta a BekahLoSurdo

Hola @BekahLoSurdo,

esa es una buena solución al problema.

Sin embargo, si lo aplica a datos grandes, podría volverse lento. Esto probablemente se ejecutará mucho más rápido y también será un poco más corto:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZZdlsIwCEb30mdPhJC/uhWP+9/GNHxEnZ4M6bxYq70mcIH4fG4U7yT3SLxvt43bg/cH0fFW+q2EzMeVAulle93+BfAVoCYFuB6X6AL9QU6BigKS9TeWgIQi2FLVjy4ADVuKx2u+8vze8Hy/LVdCEISQegj1CtCiAqUDbQ3kwFih9tD3K4DsANSbbzqDSFgi9+DZVz0jfNdGNFRTqZroNVECpS/Ct21ERORFdzX3HemLqAGbarrE3LcDzH2fAKvxEvXbK0Qyf7rG3PiJKF/G49z4IIoRFf76ruLc+JlA3Wq9x7nxE5Gt93RXc+MnoqK9VWOcGz8RCWuQxuEbH7tCHNRrJPrKxxp5e8/B6Dtn9Nzo2azJ8qVzNASriEbiWx+Ilbv0HIivfSAYb6VvU3ztg8jl40R87+9Q2idh4osfSP0Ul/jiWYz4Dt43DyRZf+D02BcpFkytqmUSsOYixYIhpOM3BtZpTIskixWlTqywo3wWWZ4yizQb07SkbRjRlUQ3FACrIaZFojXm3WK3OUx+kw1Gu4xDArNos6SDDk3MGDRMiz4zBp/jhGdaVIExGMVBNCN/HKm/GUaVsf7z4H6mvn4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t, Seconds = _t, Velocity = _t, Acceleration = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Seconds", Int64.Type}, {"Velocity", type number}, {"Acceleration", type number}, {"Index", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "GreaterThan15", each [Velocity] > 15),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"GreaterThan15"}, {{"All", each if List.First(_[GreaterThan15]) = true then Table.AddIndexColumn(_, "SprintTime",0,1) else _}}, GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index1", 0, 1),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Index", "All", {"Date", "Time", "Seconds", "Velocity", "Acceleration", "Index", "SprintTime"}, {"Date", "Time", "Seconds", "Velocity", "Acceleration", "Index", "SprintTime"}),
    AddSprintID = Table.AddColumn(#"Expanded All", "SprintID", each if [GreaterThan15] then Number.RoundUp([Index1]/2) else null),
    #"Removed Columns" = Table.RemoveColumns(AddSprintID,{"GreaterThan15", "Index1"})
in
    #"Removed Columns"

La clave está en el cuarto parámetro de la función Table.Group, donde usa GroupKind.Local. Vea este artículo sobre cómo funciona: https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

BekahLoSurdo

En respuesta a ImkeF

Esto es genial @ImkeF. No conocía GroupKind.Local y puedo ver cómo es perfecto para usar aquí y también cómo podría ser útil en otros lugares. ¡Aprecio la adición a mi kit de herramientas M!

OriK

En respuesta a ImkeF

Hola ImkeF gracias a todos, funciona perfecto y las preferencias son mucho más rebozadas.

Deja un comentario

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