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
v-ssriganesh's avatar
v-ssriganesh
Icon for Community Support rankCommunity Support
1 year ago

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

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's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    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.