Forum Discussion
Helper IPower 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.
Helper IHi 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
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:
- JeremyLeee
Great, thanks a lot.
Jeremy
- 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
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