Power Query - Loop calculating

[Anonymous]

Hi,

I have the following Excel table:

Table

The column A and B are given data. The column A represents a duration of tasks in decimal format. And the column B represents the end date of the first task in date hour decimal format. What I need is a list of end dates, adding the respective duration to the previous end date as shown in column C. Is it possible in Power Query? I was trying to use List.Accumulate(), but I couldn't use it as well...

[Ashish_Mathur]

Hi,

If you are intersted in a DAX solution, then please share the link from where i can download your PBI file.

[Anonymous]

Hi @Ashish_Mathur 

This tool will run by Excel, so I need to use the Power Query especifically. But thanks!!

[camargos88 · Accepted solution]

Hi @Anonymous ,

First, create a index column and this code for a custom column:

let _b = #"Added Index"[B]{0} in
List.Accumulate(
List.Combine(
{{_b},
List.Range(#"Added Index"[A], 0, [Index] + 1)}),
0, (s, c) => s + c)

Change the bold part with your last step name.

[Anonymous]

Hi @camargos88 ,

Yes! This is exactly what I was trying to figure out. I didn't consider using list.combine, it was what was missing for reasoning.

Anyway, I didn't understand what {_b} means, could you please explain?

And really thanks for the help!

My best regards!!

[camargos88]

Hi @Anonymous ,

I'm glad it helped.

The function List.Combine has a parameter (list of lists). So you need to convert _b to a list.. that's why {_b}.

[Anonymous]

Entendi! Obrigado @camargos88 .

Abs,

[vivran22]

Hello @Anonymous 

Is this the output you are looking for?

If yes, then you can use a custom column to add the values of the first two column.

If not, then please share more details.

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[Anonymous]

Hi @vivran22 

This is not the output that I need. In this case, I don't have the list of end dates. I just have the end of the first row (the first task). And the next rows are sequential tasks.

Therefore, the second task will end with the following operation (end date of the first line + duration of the second task).

Anyway, the friend below presented the solution that I needed.

But thanks for the help !!

Best regards!