Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

accumulated of zero

  • See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
        Fonte = Excel.Workbook(File.Contents("C:\Users\alex\Desktop\\stock_acumulated.xlsx"), null, true),
        stock_Table = Fonte{[Item="stock",Kind="Table"]}[Data],
        #"start" = Table.TransformColumnTypes(stock_Table,{{"code", Int64.Type}, {"date", type date}, {"trans_id", Int64.Type}, {"Index", Int64.Type}, {"amount", Int64.Type}, {"day amount", Int64.Type}}),
        ListOfIndex = List.Buffer(#"start"[Index]),
        ListOfamount = List.Buffer(#"start"[amount]),
        //Function Start
        fxGetAccum=(ListOfIndex, ListOfamount)=>
            let
                FunctionResult = List.Generate(()=>[x=ListOfamount{0},i=0], each [i]<List.Count(ListOfIndex), each [i=[i]+1, x=(if ListOfIndex{i}=0 then 0 else [x]) + ListOfamount{i}], each [x])
            in
                FunctionResult,
        //Function End
        Result = Table.FromColumns(Table.ToColumns(#"start")&{fxGetAccum(ListOfIndex, ListOfamount)},Table.ColumnNames(#"start")&{"Accumulated"})
    in
        Result

     

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
        Fonte = Excel.Workbook(File.Contents("C:\Users\alex\Desktop\\stock_acumulated.xlsx"), null, true),
        stock_Table = Fonte{[Item="stock",Kind="Table"]}[Data],
        #"start" = Table.TransformColumnTypes(stock_Table,{{"code", Int64.Type}, {"date", type date}, {"trans_id", Int64.Type}, {"Index", Int64.Type}, {"amount", Int64.Type}, {"day amount", Int64.Type}}),
        ListOfIndex = List.Buffer(#"start"[Index]),
        ListOfamount = List.Buffer(#"start"[amount]),
        //Function Start
        fxGetAccum=(ListOfIndex, ListOfamount)=>
            let
                FunctionResult = List.Generate(()=>[x=ListOfamount{0},i=0], each [i]<List.Count(ListOfIndex), each [i=[i]+1, x=(if ListOfIndex{i}=0 then 0 else [x]) + ListOfamount{i}], each [x])
            in
                FunctionResult,
        //Function End
        Result = Table.FromColumns(Table.ToColumns(#"start")&{fxGetAccum(ListOfIndex, ListOfamount)},Table.ColumnNames(#"start")&{"Accumulated"})
    in
        Result

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    great, where  can'I learn  this about ?