Forum Discussion
Return historic data using PowerBi query
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...
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"
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 |