Forum Discussion

Pongpon's avatar
Pongpon
Regular Visitor
5 years ago
Solved

Calculate row step like Excel

Hi Community,    I want to calculate data in Power Query like Excel example. I tried many ways but failed.    Value,     Formular,   Thank you.    
  • ziying35's avatar
    5 years ago

    try this:

    // output
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        acc = List.Accumulate(
                   Source[Column1], 
                   {null, {}}, 
                   (s,c)=>let n=s{0}??List.Sum(Source[Column1]) 
                          in {n*2.32+c-n, s{1}&{{c, n, n*2.32, n*2.32+c, n*2.32+c-n}}}
              ),
        result = Table.TransformColumns(#table(null, acc{1}), {}, each Number.Round(_, 2))
    in
        result