Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pongpon
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,

Pongpon_4-1628480831985.png

 

 

Formular,

Pongpon_5-1628480847667.png

 

Thank you.

 

 

3 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

ziying35
Impactful Individual
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

View solution in original post

11 REPLIES 11
ziying35
Impactful Individual
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

Thank you.

wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1628486705668.png

 

Hi, thank you for the help me.

I try add column number for calculate.

But the data is retrieved only for the first column.

Pongpon_2-1628857675929.png

 

Pongpon_0-1628857274358.png

Calculate like this

Pongpon_0-1629129226735.png

 

Thank you.

I want to add group in data like this, 

Pongpon_0-1628507820949.png

How to edit code?

Thank you.

wdx223_Daniel_0-1628555287915.png

 

Thank you very much.

Thank you!

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

Pongpon_1-1628497034520.png

 

your column1 is text, it should be number

Thank you, you have my life. 😊

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors