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