Forum Discussion

DSR's avatar
DSR
Icon for Resolver I rankResolver I
1 year ago

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

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 deta...
Need Help
DSR's avatar
DSR
Icon for Resolver I rankResolver I
1 year ago

 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"