List.Generate to calculate column based on the previous row value of the Same Column

[DSR]

There is no solution yet as of Sunday February 16, 2025, 7:00pm USA Eastern Time.

Hello, Please  your help is appreciated. Kindly in each line of the List.Generate write its clarification in detail.

I have a table loaded in the power query copy below. Last Step: ChangedType=Source

let

    Source = Excel.CurrentWorkbook(){[Name="Tb_CalculatebasedonPrvRowValue"]}[Content],

    ChangedType = Table.TransformColumnTypes(Source, {{"Case.CC.EEName", type text}, {"PeriodNo", Int64.Type}, {"Yr", type text}, {"NoEE.inPrvPrd", Int64.Type}, {"Yr Move Out", Int64.Type}, {"Index.inGrp", Int64.Type}, {"NoEE", Int64.Type},{"Answershallbe",Int64.Type}}),

The table has the following Columns:

Case.CC.EEName

PeriodNo

Yr

NoEE.inPrvPrd

Yr Move Out

Index.inGrp

NoEE

Answershallbe

Column [Index.inGrp] is the Index  {0,1,2,3,…10}

Case.CC.EEName	PeriodNo	Yr	NoEE.inPrvPrd	Yr Move Out	Index.inGrp	NoEE	Answershallbe
A	0	Yr00	0	2	0	0
A	1	Yr01	0	2	1	1	1
A	2	Yr02	1	2	2	1	2
A	3	Yr03	1	2	3	1	1
A	4	Yr04	1	2	4	0
A	5	Yr05	0	2	5	1	1
A	6	Yr06	1	2	6	0
A	7	Yr07	0	2	7	0
A	8	Yr08	0	2	8	0
A	9	Yr09	0	2	9	0
A	10	Yr10	0	2	10	0

Please help me in Power query

Purpose to calculate the Tenure (the length of time an employee has been working/worked in the company) for EACH Employee in a company:

an Employee stays 2 periods (column [Yr Move out]) and leaves at the End of Year 2.  A NEW employee starts the next period (if there is work)

Some Employee is hired for only 1 Period as in Yr05, Index.inGrp =5

When [NoEE]=0 means position vacant= no Employee

When [NoEE] = 1, means there is Employee on the job.

Job A is filled by different employee over the period from Yr00… to Yr10

I want to calculate the Tenure (Calculated Column value) of EACH employee, that shall meet the following conditions:

if CurrentRow[Index.inGrp] = 0  or CurrentRow[Index.inGrp] =  null then null // If Index.inGrp = 0, set value to null

else if CurrentRow[NoEE] = 0 or CurrentRow[NoEE] = null then null // If NoEE = 0 or null, set value to null                        

else if PrevValue of [NoEE]  = 0  or PrevValue of [NoEE]  = null then CurrentRow[NoEE]  // If PrevValue of [NoEE]  = 0 or null,  set value to CurrentRow[NoEE]

else if PrevValue of Tenure  =  CurrentRow[Yr Move Out] then CurrentRow[NoEE] // If PrevValue of Tenure = CurrentRow[Yr Move Out], set value to CurrentRow[NoEE]

else PrevValue of Tenure + 1, // Otherwise, PrevValue of Tenure + 1

if you do not get the result as in Column [Answershallbe] mean that it means solution is not correct.

Please use List.Generate to calculate PreValue

Tenure is the current Employee= CurrentRow Tenure

PrevValue is the Tenure of the PREVIOUS PERIOD, which could be for different empluee than the current one.

Tenure = PrevValue + CurrentRow[NoEE]

PrevValue can be sometimes 0, 1 up to [Yr Move Out]

CurrentRow[NoEE]  can be 0 or 1

Please Note: the PrevValue recursive refereeing to its previous Calculation

To create PrevValue please use List.Generate function. This function allows you to generate a list based on a set of conditions, and it can be used to create a recursive calculation where each row depends on the previous row's value.

[DSR]

 Akash_Varuna's result

was the nearest but not aligned

Thank you ffor your effort.

Yours are the nearest but they are not aligned.

I asked copilot (5-8), deepsaeek(10+) and openai (15+) times all failed. however, i took thast copilot and changed 2 line of codes to become:

CurrentRow = BufferTable{[RowIndex]+1},
PreviousRow = BufferTable{[RowIndex]},

Copilot as most looked back, i follower rickmaurinus  logic in Calculating running total.  looking  next instead of looking back

and correctly solve it: here is the correct codes:

let

    Source = Excel.CurrentWorkbook(){[Name="Tb_CalculatebasedonPrvRowValue"]}[Content],

    ChangedType = Table.TransformColumnTypes(Source, 

    {

        {"Case.CC.EEName", type text}, 

        {"PeriodNo", Int64.Type}, 

        {"Yr", type text}, 

        {"NoEE.inPrvPrd", Int64.Type}, 

        {"Yr Move Out", Int64.Type}, 

        {"Index.inGrp", Int64.Type}, 

        {"NoEE", Int64.Type},

        {"Answershallbe", Int64.Type}

    }),

BufferTable = Table.Buffer(ChangedType),

    ListTenure = List.Generate(

        () => [RowIndex = 0, Tenure = if BufferTable[NoEE]{0} = 0 then "" else BufferTable[NoEE]{0}], 

        each [RowIndex] < Table.RowCount(BufferTable),

        each [

            RowIndex = [RowIndex] + 1,

            CurrentRow = BufferTable{[RowIndex]+1},

            PreviousRow = BufferTable{[RowIndex]},

            PrevTenure = [Tenure],

            Tenure = 

                if CurrentRow[Index.inGrp] = 0 or CurrentRow[Index.inGrp] = null then ""

                else if CurrentRow[NoEE] = 0 or CurrentRow[NoEE] = null then ""

                else if PreviousRow[NoEE] = 0 or PreviousRow[NoEE] = null then CurrentRow[NoEE]

                else if PrevTenure = PreviousRow[Yr Move Out] then CurrentRow[NoEE]

                else PrevTenure + 1

        ],

        each [Tenure]

    ),

    #"CombineStartTb + TenureCol" = Table.FromColumns(

        Table.ToColumns(BufferTable) & {Value.ReplaceType(ListTenure, type {Int64.Type} )},

        Table.ColumnNames(BufferTable) & {"Calculated Tenure"}

    ),

    #"Added Custom" = Table.AddColumn(#"CombineStartTb + TenureCol", "EE.Refill", each if [Calculated Tenure] = "" then "" else if [Calculated Tenure] = 1 then List.Count(List.Select(List.FirstN(#"CombineStartTb + TenureCol"[Calculated Tenure],[Index.inGrp]+1), each _=1)) else if [Calculated Tenure]>1 then null else "", Int64.Type),

    #"Filled Down" = Table.FillDown(#"Added Custom",{"EE.Refill"})

in

    #"Filled Down"

[v-ssriganesh]

Hello @DSR,

Could you please confirm if your query have been resolved? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you.

[DSR]

Hello v-ssriganesh,

Thank you for your recommendation. but yours did not solve it. the nearest one is from Akash_Varuna but was not aligned. 

I asked copilot (5-8), deepsaeek(10+) and openai (15+) times all failed. however, i took thast copilot and changed 2 line of codes to become:

CurrentRow = BufferTable{[RowIndex]+1},
PreviousRow = BufferTable{[RowIndex]},

Copilot as most looked back, i follower rickmaurinus  logic looking next instead of lookinh back

and correctly solve it: here is the correct codes:

let

    Source = Excel.CurrentWorkbook(){[Name="Tb_CalculatebasedonPrvRowValue"]}[Content],

    ChangedType = Table.TransformColumnTypes(Source, 

    {

        {"Case.CC.EEName", type text}, 

        {"PeriodNo", Int64.Type}, 

        {"Yr", type text}, 

        {"NoEE.inPrvPrd", Int64.Type}, 

        {"Yr Move Out", Int64.Type}, 

        {"Index.inGrp", Int64.Type}, 

        {"NoEE", Int64.Type},

        {"Answershallbe", Int64.Type}

    }),

BufferTable = Table.Buffer(ChangedType),

    ListTenure = List.Generate(

        () => [RowIndex = 0, Tenure = if BufferTable[NoEE]{0} = 0 then "" else BufferTable[NoEE]{0}], 

        each [RowIndex] < Table.RowCount(BufferTable),

        each [

            RowIndex = [RowIndex] + 1,

            CurrentRow = BufferTable{[RowIndex]+1},

            PreviousRow = BufferTable{[RowIndex]},

            PrevTenure = [Tenure],

            Tenure = 

                if CurrentRow[Index.inGrp] = 0 or CurrentRow[Index.inGrp] = null then ""

                else if CurrentRow[NoEE] = 0 or CurrentRow[NoEE] = null then ""

                else if PreviousRow[NoEE] = 0 or PreviousRow[NoEE] = null then CurrentRow[NoEE]

                else if PrevTenure = PreviousRow[Yr Move Out] then CurrentRow[NoEE]

                else PrevTenure + 1

        ],

        each [Tenure]

    ),

    #"CombineStartTb + TenureCol" = Table.FromColumns(

        Table.ToColumns(BufferTable) & {Value.ReplaceType(ListTenure, type {Int64.Type} )},

        Table.ColumnNames(BufferTable) & {"Calculated Tenure"}

    ),

    #"Added Custom" = Table.AddColumn(#"CombineStartTb + TenureCol", "EE.Refill", each if [Calculated Tenure] = "" then "" else if [Calculated Tenure] = 1 then List.Count(List.Select(List.FirstN(#"CombineStartTb + TenureCol"[Calculated Tenure],[Index.inGrp]+1), each _=1)) else if [Calculated Tenure]>1 then null else "", Int64.Type),

    #"Filled Down" = Table.FillDown(#"Added Custom",{"EE.Refill"})

in

    #"Filled Down"

[v-ssriganesh]

Hi @DSR,
We appreciate your efforts and are pleased to hear that your issue was resolved. Please mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @DSR,

Thank you @dufoq3 for addressing the issue.

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If the super user's response resolved your issue, please Accept it as a solution and consider leaving a "Kudos" so other members can find it more easily.

Thank you.

[v-ssriganesh]

Hi @DSR,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[dufoq3]

At the beginning you have to check List.Generate. 

[dufoq3]

Hi @DSR, check this:

Output

let
    Source = Table.FromRows( {
        {"A", 0, "Yr00", 0, 2, 0, 0},
        {"A", 1, "Yr01", 0, 2, 1, 1},
        {"A", 2, "Yr02", 1, 2, 2, 1},
        {"A", 3, "Yr03", 1, 2, 3, 1},
        {"A", 4, "Yr04", 1, 2, 4, 0},
        {"A", 5, "Yr05", 0, 2, 5, 1},
        {"A", 6, "Yr06", 1, 2, 6, 0},
        {"A", 7, "Yr07", 0, 2, 7, 0},
        {"A", 8, "Yr08", 0, 2, 8, 0},
        {"A", 9, "Yr09", 0, 2, 9, 0},
        {"A", 10, "Yr10", 0, 2, 10, 0} },
        {"Case.CC.EEName", "PeriodNo", "Yr", "NoEE.inPrvPrd", "Yr Move Out", "Index.inGrp", "NoEE"} ),
    L = List.Buffer(Table.ToRows(Table.SelectColumns(Source,{"NoEE", "Index.inGrp", "Yr Move Out"}))),
    Gen = List.Skip(List.Generate(
        ()=> [ x = -1],
        each [x] < List.Count(L),
        each [ x = [x]+1, y = L{x},
               z = if (y{0}??0) = 0 then null
                   else if (y{1}??0) = 0 then null
                   else if ([z]??0) = 0 then 1
                   else if [z] = y{2} then y{0}
                   else [z] +1 ],
        each [z] )),
    Merged = Table.FromColumns(Table.ToColumns(Source) & {Gen}, Value.Type(Table.FirstN(Source, 0) & #table(type table[Answer=Int64.Type], {})))
in
    Merged

[DSR]

I know List.Generate as function. You did not define any of the x, y, x ??. Thanks

[dufoq3]

Read whole article please:

I've defined all of x,y and z

Just to clarify if (y{0}??0) = 0 means: if fist value of list y = null or first value of list y = null

[DSR]

Hello dufoq3, Thank you for your effort. Could you please explain for me along each line the ollowing lines?

Gen = List.Skip(List.Generate(
()=> [ x = -1],
each [x] < List.Count(L),
each [ x = [x]+1, y = L{x},
z = if (y{0}??0) = 0 then null
else if (y{1}??0) = 0 then null
else if ([z]??0) = 0 then 1
else if [z] = y{2} then y{0}
else [z] +1 ],
each [z] )),
Merged = Table.FromColumns(Table.ToColumns(Source) & {Gen}, Value.Type(Table.FirstN(Source, 0) & #table(type table[Answer=Int64.Type], {})))
in
Merged

Thank you

[v-ssriganesh]

Hello @DSR,

Thank you for posting your query in the Microsoft Fabric Community Forum regarding the calculation of a column based on the previous row's value using List.Generate in Power Query. I have successfully replicated your scenario in Power BI Desktop and would like to share the steps I followed, along with the expected output and a PBIX file for your reference.

Steps I Followed:

• Created Sample Data: I manually entered the following data into Power BI Desktop:

Case.CC.EEName	PeriodNo	Yr	NoEE.inPrvPrd	Yr Move Out	NoEE	Index.inGrp
A	0	Yr00	0	2	0	0
A	1	Yr01	0	2	1	1
A	2	Yr02	1	2	2	2
A	3	Yr03	1	2	1	3
A	4	Yr04	1	2	0	4
A	5	Yr05	0	2	1	5
A	6	Yr06	1	2	0	6
A	7	Yr07	0	2	0	7
A	8	Yr08	0	2	0	8
A	9	Yr09	0	2	0	9
A	10	Yr10	0	2	0	10

• Opened Power Query Editor: I imported the data into Power Query by selecting Transform Data.
• Added Custom Calculated Column: I implemented the following M code to create the new calculated column, CalculatedNoEE:

Code:

let 

    index = [Index.inGrp], 

    noEE = [NoEE], 

    yrMoveOut = [Yr Move Out], 

    previousValues = List.Generate( 

        () => [Value = 0, Index = 0],

        each [Index] <= index,

        each [ 

            Value = if [Index] = 0 then 0

                     else if noEE = 0 or noEE = null then null

                     else if [Value] = yrMoveOut then noEE  

                     else [Value] + 1,

            Index = [Index] + 1   

        ], 

        each [Value]

    ), 

    result = List.Last(previousValues)

in 

    result 

• Reviewed and Applied Changes: After adding the custom column, I verified the calculations.

• I have attached a screenshot of the output table showing the newly calculated column:
  
• Additionally, I have attached the PBIX file for your convenience.

If this helps, then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily.
Hope this works for you!
Thank you.

[DSR]

Hello V-ssriganesh, Thank you for your work. rom your table result your codes do not get the correct result. the Answer shall be: 

Answershallbe
1
2
1
1

I have updated the request. please review if the conditions

[Akash_Varuna]

Hi @DSR  , Could you try this 

let
// Example table
Source = Table.FromRows(
{
{"A", 0, "Yr00", 0, 2, 0, 0},
{"A", 1, "Yr01", 0, 2, 1, 1},
{"A", 2, "Yr02", 1, 2, 2, 1},
{"A", 3, "Yr03", 1, 2, 3, 1},
{"A", 4, "Yr04", 1, 2, 4, 0},
{"A", 5, "Yr05", 0, 2, 5, 1},
{"A", 6, "Yr06", 1, 2, 6, 0},
{"A", 7, "Yr07", 0, 2, 7, 0},
{"A", 8, "Yr08", 0, 2, 8, 0},
{"A", 9, "Yr09", 0, 2, 9, 0},
{"A", 10, "Yr10", 0, 2, 10, 0}
},
{"Case.CC.EEName", "PeriodNo", "Yr", "NoEE.inPrvPrd", "Yr Move Out", "Index.inGrp", "NoEE"}
),

// Generate calculated column
AddedColumn = Table.AddColumn(Source, "CalculatedColumn", each null),
ListValues = List.Generate(
() => [Index = 0, PreviousValue = 0], // Start condition
each [Index] < Table.RowCount(Source), // Continue while there are rows
each [
Index = [Index] + 1,
PreviousValue = if Table.Column(Source, "Index.inGrp"){[Index]} = 0 then
0
else if Table.Column(Source, "NoEE"){[Index]} = 0 or Table.Column(Source, "NoEE"){[Index]} = null then
null
else if [PreviousValue] = Table.Column(Source, "Yr Move Out"){[Index]} then
Table.Column(Source, "NoEE"){[Index]}
else
[PreviousValue] + 1
],
each [PreviousValue]
),

// Add ListValues back to the table
Result = Table.FromColumns(Table.ToColumns(Source) & {ListValues}, Table.ColumnNames(Source) & {"CalculatedColumn"})
in
Result
In the above 

• Recursive Calculation: List.Generate tracks the previous row's value and uses it for the next row.
• Conditions: Checks are implemented for Index.inGrp , NoEE , and Yr Move Out values as specified.
• Merge Back: The calculated list is combined back into the table.
  If this post helped please do give a kudos and accept this as a solution 
  Thanks In Advance

[DSR]

Hello Akash, Thank you for your work. I have applied your codes. Coply below. CalculatedColumn  did not answer correctly when [Index.inGrp] = 1,2,3,4,5

Case.CC.EENamePeriodNoYrNoEE.inPrvPrdYr Move OutIndex.inGrpNoEECalculatedColumn

A	0	Yr00	0	2	0	0	0
A	1	Yr01	0	2	1	1	0
A	2	Yr02	1	2	2	1	1
A	3	Yr03	1	2	3	1	2
A	4	Yr04	1	2	4	0	1
A	5	Yr05	0	2	5	1	null
A	6	Yr06	1	2	6	0	null
A	7	Yr07	0	2	7	0	null
A	8	Yr08	0	2	8	0	null
A	9	Yr09	0	2	9	0	null
A	10	Yr10	0	2	10	0	null

[DSR]

Hello Akash,

If I modify the conditions to become:

if Table.Column(ChangedType, "Index.inGrp"){[Index]} = 0 then null
else if Table.Column(ChangedType, "NoEE"){[Index]} = 0 or Table.Column(ChangedType, "NoEE"){[Index]} = null then null
else if [PreviousValue] = 0 or [PreviousValue] = null then 1
else if [PreviousValue] = Table.Column(ChangedType, "Yr Move Out"){[Index]} then Table.Column(ChangedType, "NoEE"){[Index]}
else [PreviousValue] + 1 ],

=======

It resulted in correct numbers but, not aligned,  shifted one row down.

Yr Move OutIndex.inGrpNoEEAnswershallbeCalculatedColumn

2	0	0	null	0
2	1	1	1	null
2	2	1	2	1
2	3	1	1	2
2	4	0	null	1
2	5	1	1	null
2	6	0	null	1
2	7	0	null	null
2	8	0	null	null
2	9	0	null	null
2	10	0	null	null