Forum Discussion

JanoLehocky's avatar
JanoLehocky
Helper I
4 years ago
Solved

Table.AddColumn help - adding more than one previous row column

Good afternoon Power BI Community,   I have a case where I need to create two custom columns that shows the previou value of two different columns within each sub group.   i.e.: in my case I need...
Need Help
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi JanoLehocky ,

    You can try this query to get the previous row value in Power Query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZRNT4NAEIb/yoRzD9BKhaO1msbUj7QkHpoeRpjYDQvbLLs2/HsHPChQEzTBVg6b5d152Hdn2NlsnMlk4oycmRbGYF7Cek+oC1bWKFELSnjquTzcE4fAit4EHZztqB/o8fCkVaZqIggCfr+RUihj4FpJyjBvEg8VgCUUMUoCkceasKC/ox/NjvSgwDf+PNd163TamGBJxPMra9QeRQILZbUsWfkR/rUO40YdegR3qh16Psu3WB0XIpG1oel/hMKQ5YV93cEzyRfOcqteXtCocL/4896miwVhraNOITqgaEPHMzcEdHliqH545c4WRuQQaZsQ2hZ68Xu0apdzypQRKv+4h+PqayuVsABLm+IRm5+/RZ/ojrMz2WJgX9N2Zn2/agdzQYbbwUxinvYxdXKq2/H9Drt9Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Personnel no." = _t, #"Personnel Number" = _t, #"EE subgroup name" = _t, #"Salary Band" = _t, #"Reason for Changing Master Dat" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Personnel no.", Int64.Type}, {"Personnel Number", type text}, {"EE subgroup name", type text}, {"Salary Band", type text}, {"Reason for Changing Master Dat", type text}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", {"Personnel no."}, 
            {
                {
                    "Data", each 
                    let tab=Table.AddIndexColumn(_,"Index",1,1,Int64.Type) in
                        Table.AddColumn(
                            tab,"New",
                            (x)=> try Table.Max(Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[EE subgroup name]
                                    otherwise null ), 
                type table [#"Personnel no."=nullable number, Personnel Number=nullable text, EE subgroup name=nullable text, Salary Band=nullable text, Reason for Changing Master Dat=nullable text]
                }
            }
        )
in
    #"Grouped Rows"

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
4 years ago

While this can be done within your query, it would be much easier to do with a DAX calculated column.  Please provide some sample/mock data in a copy/paste-able format (or link to data), so specific DAX expressions can be suggested.

 

Pat

    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      4 years ago

      Your data didn't have a date or index column to know which is the previous, so I added an Index column in the query editor.  Once that is in place, you can make two columns with this pattern to get the previous values.  Replace Personnel with your actual table name.

       

      Prev Band =
      VAR thisperson = Personnel[Personnel no.]
      VAR thisindex = Personnel[Index]
      RETURN
          MINX (
              FILTER (
                  Personnel,
                  Personnel[Personnel no.] = thisperson
                      && Personnel[Index] = thisindex - 1
              ),
              Personnel[Salary Band]
          )

       

      Pat