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.
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
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