Getting prior record

[michaelu1]

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

[ronrsnfld · Accepted solution]

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

[michaelu1]

@ronrsnfld I know that my data is not sorted correctly but that should be easy to rectify, I think this is what I was looking for.

Thank you!

[michaelu1]

@ronrsnfld Thank you again, this solution has been working amazing!

Are you able to help me again by adding another column to this for the reverse? Meaning showing me the next lease?

[michaelu1]

Actually, I just played around some more and figured it out:

#"Grouped Rows" = Table.Group(#"Removed Columns1", {"UnitID"}, {
        {"Shifted Column", (t)=>
            Table.FromColumns(
                Table.ToColumns(t)
                    & {{null} & List.RemoveLastN(t[RMLEASE],1)} & {List.RemoveFirstN(t[RMLEASE],1) & {null}},{"UnitID","RMLEASE","Prior Lease", "Next Lease"}),
                type table[UnitID=text,RMLEASE=Int64.Type, Prior Lease = Int64.Type, Next Lease = Int64.Type]}       
    }),

[michaelu1]

@HotChilli 

Thats an interesting solution which should work. I'm a little hesitant though, as the the SQL query to generate the list of tenants is a rather large query so I would like to avoid having to load it twice.

Is there any way to accomplish this within 1 query?

[HotChilli]

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