Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
EnergyJoel
New Member

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

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

 

“Historical_Data_Table":

DATECloud%Wind_KMSolar_UtilizPrice
2023-01-010.8500.14.5
2023-01-020.8500.14.5
2023-01-030.9515010
2023-01-040.951508
2023-01-050.6250.356
2023-01-060.6250.356
2023-01-070.2550.86
2023-01-080.2550.87
2023-01-090.55100.55.5
2023-01-100.55100.55.5
2023-01-110.28120.62
2023-01-120.28120.62
2023-01-130.1400.93
2023-01-140.1400.93
2023-01-150.33170.78
2023-01-160.01170.951
2023-01-170.01170.951

 

“Forecast_TBL” (contains my weather forecasts and 3 buckets composed of Max and Min Requirments):

 

DateFcst_CloudFcst_WindFcst_SolarMax_CloudMin_CloudMax_WindMin_WindMax_SolarMin_SolarCountMinMaxModeMedianAverage
10.5120.50.70.327-30.750.2555.58666.2
20.8100.110.625-50.35-0.1564.5104.566.5
30.15150.80.35-0.053001.050.55518222.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)

 

EnergyJoel_0-1674424710065.png

 

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. 

 

 

2 REPLIES 2
KT_Bsmart2gethe
Impactful Individual
Impactful Individual

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

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.