Forum Discussion
Getting prior record
I'm trying to get the prior LeaseID in power query for each record on my table.
You can see how the LeaseIDs don't neccesarily start at 1 and are also not consecutive.
For each unit and lease, and I'm trying to get the prior lease.
Here is the data:
Here is the expected result:
(FYI the actual data has 200k+ records)
Here is a sample file:
https://drive.google.com/file/d/1yil8zqNSnEwYT5P5mGZeSnah8PyNjehA/view?usp=sharing
Group by UnitID, then add a shifted column to each subtable.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQw0HU0VNJRMlSK1UHiGqFyjVG5JnCuE6peJ1TFYK4pKtcMwTVCVWyEarIRpmILpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UnitID = _t, LeaseID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"UnitID", type text}, {"LeaseID", Int64.Type}}), //Group by UnitID // then add Shifted Column to each sub table and re-expand #"Group UnitID" = Table.Group(#"Changed Type",{"UnitID"}, { {"Shifted Column", (t)=> Table.FromColumns( Table.ToColumns(t) & {{null} & List.RemoveLastN(t[LeaseID],1)},{"UnitID","LeaseID","Prior Lease"}), type table[UnitID=text,LeaseID=Int64.Type, Prior Lease = Int64.Type]} }), #"Expanded Shifted Column" = Table.ExpandTableColumn(#"Group UnitID", "Shifted Column", {"LeaseID", "Prior Lease"}) in #"Expanded Shifted Column"Results
6 Replies
You could start with 2 versions of the table.
In version 1, add an Index column (from 0)
In version 2, add an Index column (from 1).
Merge the 2 tables (on Index AND UnitID) using Left Outer join with Version 1 as first table.
Expand to bring back Lease ID
Group by UnitID, then add a shifted column to each subtable.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQw0HU0VNJRMlSK1UHiGqFyjVG5JnCuE6peJ1TFYK4pKtcMwTVCVWyEarIRpmILpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UnitID = _t, LeaseID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"UnitID", type text}, {"LeaseID", Int64.Type}}), //Group by UnitID // then add Shifted Column to each sub table and re-expand #"Group UnitID" = Table.Group(#"Changed Type",{"UnitID"}, { {"Shifted Column", (t)=> Table.FromColumns( Table.ToColumns(t) & {{null} & List.RemoveLastN(t[LeaseID],1)},{"UnitID","LeaseID","Prior Lease"}), type table[UnitID=text,LeaseID=Int64.Type, Prior Lease = Int64.Type]} }), #"Expanded Shifted Column" = Table.ExpandTableColumn(#"Group UnitID", "Shifted Column", {"LeaseID", "Prior Lease"}) in #"Expanded Shifted Column"Results
