Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have power meeter readings from from 61 (M05-M65) sensors reporting every 10 min for a one week period, a total of 70875 records in my excel table. A meeter reading is reported for each sensor along with a time stamp.
I use the following formula to calculate a collumn with the difference between two consecutive meeter readings for a sensor to determine the power consumption for that sensor in that interval:
Increment_ = (IF(
ISBLANK(
calculate(
MAX(LCU_data[Power meter (Wm)]); ALL(LCU_data); LCU_data[Pole]=EARLIER(LCU_data[Pole]); LCU_data[RTC]<EARLIER(LCU_data[RTC])));0;
LCU_data[Power meter (Wm)]-calculate(MAX(LCU_data[Power meter (Wm)]); ALL(LCU_data);
LCU_data[Pole]=EARLIER(LCU_data[Pole]); LCU_data[RTC]<EARLIER(LCU_data[RTC]))))
No suprise i run out of memory.
If i delete data from all but one sensor in excel and then import as a smaller table and alter the code accordingly i get what i want:
Increment_ = (IF(
ISBLANK(
calculate(
MAX(M05[Power meter (Wm)]); ALL(M05); M05[Pole]=EARLIER(M05[Pole]); M05[RTC]<EARLIER(M05[RTC])));0;
M05[Power meter (Wm)]-calculate(MAX(M05[Power meter (Wm)]); ALL(M05);
M05[Pole]=EARLIER(M05[Pole]); M05[RTC]<EARLIER(M05[RTC]))))
I cant make individual tables for each sensor, but is there a way to do this calculation sequentially per sensor and not get into the issue with the EARLIER function running out of memory when performed on the whole table?
Solved! Go to Solution.
I would suggest doing this in the query editor with the following general steps :
1. start query1
2. sort by Pole and RTC columns (only if they aren't already sorted)
3. add an index column
4. add a custom column called "previndex" with the formula: [index] - 1
5. duplicate the query to query2
6. merge query1 and query2 on query1[previndex] = query2[index] and query1[Pole] = query2[Pole]
7. calculate the difference between the two [Power meter (Wm)] columns
I would suggest doing this in the query editor with the following general steps :
1. start query1
2. sort by Pole and RTC columns (only if they aren't already sorted)
3. add an index column
4. add a custom column called "previndex" with the formula: [index] - 1
5. duplicate the query to query2
6. merge query1 and query2 on query1[previndex] = query2[index] and query1[Pole] = query2[Pole]
7. calculate the difference between the two [Power meter (Wm)] columns
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |