Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |