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

[DSR]

Hello,

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", Int64.Type}, {"NoEE.inPrvPrd", Int64.Type}, {"Yr Move Out", Int64.Type}, {"Index.inGrp", Int64.Type}, {"Index.inGrp", 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 by using:

List.Generate to calculate column based on the previous row value of the sale column I am calculating.

The calculated column value shall meet the following conditions:

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

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

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

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

else [PrevValue] + 1, // Otherwise, [PrevValue] + 1

Please Note: the calculated column is recursive refereeing to its previous Calculation

To create a calculated column in Power Query that meets the conditions you specified, you can use the 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.

[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

[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