Power Query: Average, Max, Min using filters and conditions

[EnergyJoel]

I’m new to Power Query and have gotten stuck for the last few weeks trying to figure this out.

“Historical_Data_Table":

DATE	Cloud%	Wind_KM	Solar_Utiliz	Price
2023-01-01	0.85	0	0.1	4.5
2023-01-02	0.85	0	0.1	4.5
2023-01-03	0.95	15	0	10
2023-01-04	0.95	15	0	8
2023-01-05	0.6	25	0.35	6
2023-01-06	0.6	25	0.35	6
2023-01-07	0.2	55	0.8	6
2023-01-08	0.2	55	0.8	7
2023-01-09	0.55	10	0.5	5.5
2023-01-10	0.55	10	0.5	5.5
2023-01-11	0.28	12	0.6	2
2023-01-12	0.28	12	0.6	2
2023-01-13	0.1	40	0.9	3
2023-01-14	0.1	40	0.9	3
2023-01-15	0.33	17	0.7	8
2023-01-16	0.01	17	0.95	1
2023-01-17	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 the 6 columns for illustration)

The filter criteria for calculating the count, min, max, mode, median, and average are driven by the 3 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)

For Date "2" in Forecast_Tbl, The “count” column tells me it's found 6 price points in the "Historical_data_table” that satisfied my 6 different criteria. Based on those same criteria it found a “min” price of $4.50, max price of $10.00, mode of 4.5 and median of $6.00, and average price of $6.50, based on those 6 price points.

The following are my six dynamic filtering criteria:

1.Historical_Data_Table[cloud%] <= Forecast_tbl[max cloud]

2.Historical_Data_Table[cloud%] >= Forecast_tbl[min cloud]

3.Historical_Data_Table[Wind_km] <= Forecast_tbl[max wind]

4.Historical_Data_Table[Wind_km] >= Forecast_tbl[min wind]

5.Historical_Data_Table[SolarUtiliz] <= Forecast_tbl[max solar]

6.Historical_Data_Table[SolarUtiliz] >= Forecast_tbl[min solar]

Any help would be greatly appreciated as I have tried using different filters and lookups in Power Query but haven't been successful. 

[KT_Bsmart2gethe]

Hi @EnergyJoel ,

For the additional 6 columns, which table are we using for the calculation? i.e. how you get the count, min, max, etc as per the forecast screentshot.

Regards

KT

[EnergyJoel]

Hi KT,

The additional 6 columns will be on the "forecast_tbl" (The Forecast_Tbl in post above includes those 6 rows for illustration) the filter criteria for calculating the average are driven by the 3 different min/max columns in "forecast_tbl" as filters for looking up the historical_data points and the correlating price for the matching data points in the “historical_data” table

It will do this for each row in my forecast_tbl, essentially each row of data is a different forecast criteria.

For example in the date "1" of Forecast_tbl, I am telling the "count" column to lookup and count all the Historical data [Price] occurances (in the "historical data" table) where the following six criteria are met:

1.Historical data[cloud%] <= Forecast_tbl[max cloud]

2.Historical data[cloud%] >= Forecast_tbl[min cloud] 

3.Historical data[Wind_km] <= Forecast_tbl[max wind]

4.Historical data[Wind_km] >= Forecast_tbl[min wind]

5.Historical data[SolarUtiliz] <= Forecast_tbl[max solar]

6.Historical data[SolarUtiliz] >= Forecast_tbl[min solar] 

It will then do this same procedure for the other five columns (max,min,mode,median,average) using the same criteria to filter the historical data table and provide me with the column [Price] in historical_data of those criteria met.

Appreciate the help!