I’m new to Power Query and have gotten stuck for the last few weeks trying to figure this out. I have two tables...
- Historical_Data_Table (contains all my historical datapoints that I want to filter to determine different prices):
| DATE | Cloud% | Wind_KM | Solar_Utiliz | Price |
|---|---|---|---|---|
| 01-Jan | 0.85 | 0 | 0.1 | 4.5 |
| 02-Jan | 0.85 | 0 | 0.1 | 4.5 |
| 03-Jan | 0.95 | 15 | 0 | 10 |
| 04-Jan | 0.95 | 15 | 0 | 8 |
| 05-Jan | 0.6 | 25 | 0.35 | 6 |
| 06-Jan | 0.6 | 25 | 0.35 | 6 |
| 07-Jan | 0.2 | 55 | 0.8 | 6 |
| 08-Jan | 0.2 | 55 | 0.8 | 7 |
| 09-Jan | 0.55 | 10 | 0.5 | 5.5 |
| 10-Jan | 0.55 | 10 | 0.5 | 5.5 |
| 11-Jan | 0.28 | 12 | 0.6 | 2 |
| 12-Jan | 0.28 | 12 | 0.6 | 2 |
| 13-Jan | 0.1 | 40 | 0.9 | 3 |
| 14-Jan | 0.1 | 40 | 0.9 | 3 |
| 15-Jan | 0.33 | 17 | 0.7 | 8 |
| 16-Jan | 0.01 | 17 | 0.95 | 1 |
| 17-Jan | 0.01 | 17 | 0.95 | 1 |
- Forecast_Tbl (contains my weather forecasts and 3 buckets composed of Max and Min Requirments):
| Date | Fcst_Cloud | Fcst_Wind | Fcst_Solar | Max_Cloud | Min_Cloud | Max_Wind | Min_Wind | Max_Solar | Min_Solar | Count | Min | Max | Mode | Median | Average |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 0.5 | 12 | 0.5 | 0.7 | 0.3 | 27 | -3 | 0.75 | 0.25 | 5 | 5.5 | 8 | 6 | 6 | 6.2 |
| 2 | 0.8 | 10 | 0.1 | 1 | 0.6 | 25 | -5 | 0.35 | -0.15 | 6 | 4.5 | 10 | 4.5 | 6 | 6.5 |
| 3 | 0.15 | 15 | 0.8 | 0.35 | -0.05 | 30 | 0 | 1.05 | 0.55 | 5 | 1 | 8 | 2 | 2 | 2.8 |
This is where I've gotten stumped. I want to add 6 additional columns COUNT, MIN, MAX, MODE, MEDIAN, AVERAGE (The Forecast_Tbl above INCLUDES these 6 columns (Count:Average) and expected outputs for illustration)
The filter criteria for calculating the count, min, max, mode, median, and average are driven by the 6 different min/max columns in "forecast_tbl" as filters for looking up the historical_data_table points and the corresponding Historical_Data_Table[Price].
It will do this for each row in my forecast_tbl (essentially each row in the Forecast_tbl is a different price forecast).
For example, for Date "1" in Forecast_Tbl, The “count” column tells me it's found 5 price points in the "Historical_data_table” that satisfied my 6 criteria:
- (Historical_Data_Table[Cloud%]<=.70)
- (Historical_Data_Table[Cloud%]>=.30)
- (Historical_Data_Table[Wind_KM]<=27)
- (Historical_Data_Table[Wind_KM]>=-3)
- (Historical_Data_Table[Solar_Utiliz]<=.75)
- (Historical_Data_Table[Solar_Utiliz]>=.25)
Based on those same criteria it found a MIN price of $5.50, MAX price of $8.00, MODE and MEDIAN of $6.00, and AVERAGE price of $6.20, based on those 5 historical price data points. (see my COUNT/FILTER formula in picture below)
