Forum Discussion

michaelu1's avatar
michaelu1
Advocate II
2 years ago
Solved

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...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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's avatar
michaelu1
Advocate II
1 year ago

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's avatar
michaelu1
Advocate II
1 year ago

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]}       
    }),