Devolver datos históricos mediante la consulta PowerBi

Syndicate_Admin · ‎12-15-2022

Hola, estoy tratando de mostrar datos históricos basados en varios filtros y luchando por obtener una consulta de búsqueda que funcione.

Por ejemplo, tengo una tabla con;

WORKNO	UBICACIÓN	PLAN DE TRABAJO	ESTADO	FECHA	FECHA DE CIERRE ANTERIOR
1234	AB-12	E1001	ABRIR	01/02/22	??
4321	AB-12	E2001	CERRADO	01/01/22
5678	AB-12	E1001	CERRADO	01/11/21
8765	AB-12	E2001	CERRADO	01/01/21

Para WORKNO 1234 quiero buscar los WORKNO completados anteriormente y devolver la FECHA en que se cerró el PLAN DE TRABAJO E1001 más reciente, para la tabla anterior esto debería devolver 01/11/21. He probado varias funciones relacionadas con las últimas fechas completadas y los valores máximos, pero como el E1001 más reciente no se ajusta a ninguna de esas consultas, estoy luchando para devolver el valor correcto.

¡Cualquier ayuda sería apreciada!

Syndicate_Admin · ‎12-15-2022

Su conjunto de prueba es algo limitado, pero esto debería hacer el truco.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxJCoAwEETRq0ivlaTaTFuH7MQILkPufw0bZ8TVh+JRORO4NVRT1zdgaYTWkKYlzhINpVkxU6kzmZbxpnzQYUprHE+MC1vnw8/vG0Mwdhy8s9/n6tbV8y1rKRs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WORKNO = _t, LOCATION = _t, #"JOB PLAN" = _t, STATUS = _t, DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}}),
    Base = Table.Buffer(Table.Sort(#"Changed Type",{{"DATE", Order.Descending}})),
    #"Grouped Rows" = Table.Group(Base, {"JOB PLAN"}, {{"Data", each _, type table [WORKNO=nullable text, LOCATION=nullable text, JOB PLAN=nullable text, STATUS=nullable text, DATE=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 0, 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    IndexTable = Table.Buffer(Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"WORKNO", "LOCATION", "JOB PLAN", "STATUS", "DATE", "Index"}, {"WORKNO", "LOCATION", "JOB PLAN", "STATUS", "DATE", "Index"})),
    #"Added Custom1" = Table.AddColumn(IndexTable, "Custom", (outer)=> Table.SelectRows(IndexTable, each ([JOB PLAN]= outer[JOB PLAN]) and ([Index] = outer[Index]+1))[DATE]{0}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom1", {{"Custom", null}})
in
    #"Replaced Errors"

Syndicate_Admin · ‎12-16-2022

Gracias por la respuesta JW, parece que esto funcionará, ¡aprecie que el conjunto de datos de prueba se simplificó ya que no se pudo publicar el conjunto real como más de 14mil filas!

He seguido los pasos anteriores con mi conjunto de datos, cuando llego al siguiente paso;

 #"Added Custom1" = Table.AddColumn(IndexTable, "Custom", (outer)=> Table.SelectRows(IndexTable, each ([JOB PLAN]= outer[JOB PLAN]) and ([Index] = outer[Index]+1))[DATE]{0}),

No obtengo valores de error o fecha como ejemplo, la columna 'Personalizado' solo muestra 'Tabla' en cada celda, sin valores reales.

El enfoque final es ejecutar esto para múltiples ubicaciones con el mismo PLAN DE TRABAJO, ¿seguirá funcionando este enfoque si el conjunto de datos se ve como el siguiente?

WORKNO	UBICACIÓN	PLAN DE TRABAJO	ESTADO	FECHA	FECHA DE CIERRE ANTERIOR
1234	AB-12	E1001	ABRIR	01/02/22	??
4321	AB-12	E2001	CERRADO	01/01/22
5678	AB-12	E1001	CERRADO	01/11/21
8765	AB-12	E2001	CERRADO	01/01/21
2345	CD-34	E1001	ABRIR	05/06/22	??
6789	CD-34	E1001	CERRADO	01/05/21

Devolver datos históricos mediante la consulta PowerBi