cancel
Showing results for
Did you mean:
Regular Visitor

## 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.

3 ACCEPTED SOLUTIONS
Super User
Impactful Individual

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``````
Super User
11 REPLIES 11
Impactful Individual

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``````
Regular Visitor

Thank you.

Super User
Regular Visitor

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.

Regular Visitor

I want to add group in data like this,

How to edit code?

Thank you.

Super User
Regular Visitor

Thank you very much.

Regular Visitor

Thank you!

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

Super User

your column1 is text, it should be number

Regular Visitor

Thank you, you have my life. 😊

New Member

Great, Can you send .pbix file to my email. (namphon.chailungka@gmail.com)
Thank you so much.

Announcements