Power Query reference later added column

[JeremyLeee]

Hi All,

I want to create a new column "Current Max" like what we did in excel below:

but we can't reference to steps (new columns) created later, is there a way to achieve this in Powe BI?

Thanks.

[Anonymous]

Not entirely clear on what makes a new max. Any chance you can load some more data that shows exactly what you are looking to accomplish?  This can more than likely be done in Powe Query

[JeremyLeee]

Hi Nick,

Thanks for your quick response, for clarificaiton:

A New Max is 1 when current SOC is larger than the previous Current Max;

A Deep Cycle is 1 when Current Max is larger than current SOC.

Thanks,

Jeremy

[Anonymous]

@JeremyLeee ,

Let's see how this goes. I will attach the pbix file below since this is on done in Power Query. But here's what I did:

• Added an index column starting at 0
• Added a new column that will take the max of SOC starting from index 0 (row 1) till the current rows index, which will be 1 less than the current actual row #

if [Index] = 0
then
[Array SOC]
else
List.Max(
List.Range(#"Added Index"[Array SOC],0,[Index]),1)

• Added a column for new max if the current soc > max till current row give a 1, else a zero. Unless the index is 0 ( which would be the first row) and then by default that is the max
• added a column for deep cycle if the max til the current row > current array soc
• Removed the index column and Max till current row columns. Dont need to delete them but no sense in keeping them if they arent used elsewhere

File:

https://1drv.ms/u/s!Amqd8ArUSwDS0wCifSvJHEtWbIbk

[JeremyLeee]

@Anonymous 

Hi Nick, one more thing to address, the current solution can't fully achieve the goal since Current Max is also determined by Deep Cycle: if there is a deep cycle = 1 in previous row, the current max should recount, please see my first screen shot.

Deep Cycle is 1 when (Current Max - SOC) > 70. so basically as this achieved, the Current Max should reset.

Thanks again,

Jiaming 

[JeremyLeee]

For example:

The first apperance of Deep Cycle = 1 is correct, but afterwards Max Till Current Row should be reset to value of SOC, so that there won't be 1 all the way to the end

[Anonymous]

@JeremyLeee 

Wasn't 100% clear to me, but here's what I got. 

• Added a TRUE/FALSE column if the Max till Current Row - Current SOC > 0
• Added another column using List.Range and the Index column to get the previous column TRUE FALSE
• New Column for DeepCycle which looks for TRUE in the 1st column we added and then False in the second column
• New column that if Deep Cycle = 1 then give that Rows SOC value, otherwise null. 
• Then filled down that column so the value of the SOC column when Deep Cycle was 1 is the value in the rows below it and not null

Also had to account if the index was 0 (since that was the 1st row)

Here's the final table. Made some changes to the Array SOC column so I could see if it was working like I expected. You wouldn't need all these columns and could really compress all these steps into just a few, or even better a function. But left them all there so you could see

PBIX file:

https://1drv.ms/u/s!Amqd8ArUSwDS0ywHNzHBP1ao--Eu

[JeremyLeee]

@AnonymousHi Nick,

Thanks for your response, the idea is "Max till Current Row" should be reset to current SOC once there's a deep cycle achieved.

In your solution, it works since row 7 (SOC = 800) is bigger than row 2 (SOC = 700), but if I change the value of row 7 to 600, the deep cycle would not be detected.

There should be a deep cycle = 1 in row 8 since 600-66.17>70.

Thanks again.

[JeremyLeee · Accepted solution]

Just want to follow up on my previous post, rather than add ton of steps and still can't achieve the goal, at the end I just wrote a short python scripts using for loop and while loop then run it in Power Query Editor to add all columns I want.

[JeremyLeee]

Great, thanks a lot.

Jeremy