Return historic data using PowerBi query

[jmupton]

Hi, i'm trying to display historic data based on several filters and struggling to get a search query that will work.

For example I have a table with;

WORKNO	LOCATION	JOB PLAN	STATUS	DATE	PREVIOUS CLOSED DATE
1234	AB-12	E1001	OPEN	01/02/22	??
4321	AB-12	E2001	CLOSED	01/01/22
5678	AB-12	E1001	CLOSED	01/11/21
8765	AB-12	E2001	CLOSED	01/01/21

For WORKNO 1234 I want to search the previously completed WORKNO's and return the DATE the most recent E1001 JOB PLAN was CLOSED, for the above table this should return 01/11/21. I have tried several functions related to last completed dates and max values, but as the most recent E1001 does not fit either of those queries i'm struggling to return the correct value.

Any help would be appreciated!

[JW_van_Holst]

Your test set is somewhat limited, but this should do the trick.

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"

[jmupton]

Thanks for the response JW, looks like this will work, appreciate the test data set was simplified as couldn't post the real set as over 14mil rows!

I have followed through the steps above with my dataset, when i get to the following step;

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

I do not get Error or date values as your example, the 'Custom' column just displays 'Table' in every cell, no actual values.

The final approach is to run this for multiple locations with the same JOB PLAN, will this approach still work if the dataset looks like the below?

WORKNO	LOCATION	JOB PLAN	STATUS	DATE	PREVIOUS CLOSED DATE
1234	AB-12	E1001	OPEN	01/02/22	??
4321	AB-12	E2001	CLOSED	01/01/22
5678	AB-12	E1001	CLOSED	01/11/21
8765	AB-12	E2001	CLOSED	01/01/21
2345	CD-34	E1001	OPEN	05/06/22	??
6789	CD-34	E1001	CLOSED	01/05/21