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.    
Need Help
  • ziying35's avatar
    ziying35
    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
ziying35's avatar
ziying35
Impactful Individual
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
Pongpon's avatar
Pongpon
Regular Visitor
5 years ago

Thank you.