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

[JanoLehocky]

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 to know the previous row for each employee to understand:

1. what the previous salary band was

2. what the previous employee subgroup was (autopaid hrly, salaried, etc,,)

I was super happy to find this video:

https://www.youtube.com/watch?v=IGF2-qfzDQs&t=796s

All went well and I was able to create my previous salary band column. However... How to create a second previous row column for EE Subgroup?

THIS WAS THE CODE TO CREATE THE PrevRowSB

= Table.AddColumn(Custom1, "Custom", each let
AllDataTable = [FullTable],
PrevRowValue =
Table.AddColumn(
AllDataTable, "PrevRowSB",
each try AllDataTable[Salary Band] {[Index] - 1}
otherwise null
)
in
PrevRowValue)

I just want to have an additional column called 'PrevEEsubgroupname' that for above should list:

null

Salaried

Salaried

Salaried

Salaried

Salaried

Would appreciate any assistance with this.. 

Thank you

Jano

[v-yingjl · Accepted solution]

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]

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

[JanoLehocky]

Hello Pat,

Thanks for offering your knowledge here.

Here is some sample data:

https://docs.google.com/spreadsheets/d/1xk_yEhXPLVnvf7ki59lvXgYbLxqfiak1/edit?usp=sharing&ouid=10053...

Cheers

Jano

[mahoneypat]

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