Calculate row step like Excel

[Pongpon]

Hi Community, 

I want to calculate data in Power Query like Excel example. I tried many ways but failed. 

Value,

Formular,

Thank you.

[wdx223_Daniel · Accepted solution]

[ziying35 · Accepted solution]

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 · Accepted solution]

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]

Thank you.

[wdx223_Daniel · Accepted solution]

[Pongpon]

Hi, thank you for the help me.

I try add column number for calculate.

But the data is retrieved only for the first column.

Calculate like this

Thank you.

[Pongpon]

I want to add group in data like this, 

How to edit code?

Thank you.

[wdx223_Daniel · Accepted solution]

[Pongpon]

Thank you very much.

[Pongpon]

Thank you!

But i put same code in Power Query for Power BI, Not work.

[wdx223_Daniel]

your column1 is text, it should be number

[Pongpon]

Thank you, you have my life. 😊

[Anonymous]

Great, Can you send .pbix file to my email. ([email protected])
Thank you so much.