Forum Discussion
Power Query reference later added column
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.
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:
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
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
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
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.