Forum Discussion

michaelu1's avatar
michaelu1
Icon for Advocate II rankAdvocate 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

     

     

     

ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper User
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
    Icon for Advocate II rankAdvocate II
    2 years ago

    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's avatar
      michaelu1
      Icon for Advocate II rankAdvocate 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
        Icon for Advocate II rankAdvocate 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]}       
    }),