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.
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
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
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:
Great, thanks a lot.
Jeremy