Search code examples
filterdatatablepowerbipowerquery

Table Row returning blank when Filtered Criteria not met on 2nd table

I have the following two tables:

Historical_Data_Tbl:

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:

Date Fcst_Cloud Fcst_Wind Fcst_Solar Max_Cloud Min_Cloud Max_Wind Min_Wind Max_Solar Min_Solar
1 0.5 12 0.5 0.7 0.3 27 -3 0.75 0.25
2 0.8 10 0.1 1 0.6 25 -5 0.35 -0.15
3 0.15 15 0.8 0.35 -0.05 30 0 1.05 0.55
4 0.75 10 0.2 0.95 0.55 25 -5 0.45 -0.05
5 0.1 99 0.99 0.3 -0.1 114 84 1.24 0.74
6 0.11 35 0.8 0.31 -0.09 50 20 1.05 0.55

CODE BELOW:

let

//Read in Historical table and set data types
    Source = Excel.CurrentWorkbook(){[Name="Historical"]}[Content],
    Historical = Table.Buffer(Table.TransformColumnTypes(Source,{
        {"DATE", type date}, {"Cloud%", type number}, {"Wind_KM", Int64.Type}, 
        {"Solar_Utiliz", type number}, {"Price", type number}})),

//Read in Forecast table anda set data types
    Source1 = Excel.CurrentWorkbook(){[Name="Forecast"]}[Content],
    Forecast = Table.Buffer(Table.TransformColumnTypes(Source1,{
        {"Date", Int64.Type}, {"Fcst_Cloud", type number}, {"Fcst_Wind", Int64.Type}, 
        {"Fcst_Solar", type number}, {"Max_Cloud", type number}, 
        {"Min_Cloud", type number}, {"Max_Wind", Int64.Type}, {"Min_Wind", Int64.Type}, 
        {"Max_Solar", type number}, {"Min_Solar", type number}})),

//Generate list of filtered Historical Table for each row in Forecast Table with aggregations
//Merge aggregations with the associated Forecast row
    #"Filtered Historical" = List.Generate(
        ()=>[t=Table.SelectRows(Historical, (h)=> 
                    h[#"Cloud%"] <= Forecast[Max_Cloud]{0} and h[#"Cloud%"]>= Forecast[Min_Cloud]{0}
                and h[Wind_KM] <= Forecast[Max_Wind]{0} and h[Wind_KM] >= Forecast[Min_Wind]{0}
                and h[Solar_Utiliz] <= Forecast[Max_Solar]{0} and h[Solar_Utiliz] >= Forecast[Min_Solar]{0}),
                idx=0],
            each [idx] < Table.RowCount(Forecast),
            each [t=Table.SelectRows(Historical, (h)=> 
                    h[#"Cloud%"] <= Forecast[Max_Cloud]{[idx]+1} and h[#"Cloud%"]>= Forecast[Min_Cloud]{[idx]+1}
                and h[Wind_KM] <= Forecast[Max_Wind]{[idx]+1} and h[Wind_KM] >= Forecast[Min_Wind]{[idx]+1}
                and h[Solar_Utiliz] <= Forecast[Max_Solar]{[idx]+1} and h[Solar_Utiliz] >= Forecast[Min_Solar]{[idx]+1}),
                idx=[idx]+1],
            each Forecast{[idx]} & Record.FromList(
                {List.Count([t][Price]),List.Min([t][Price]), List.Max([t][Price]),
                  List.Modes([t][Price]){0}, List.Median([t][Price]), List.Average([t][Price])}, 
                    {"Count","Min","Max","Mode","Median","Average"})),

    #"Converted to Table" = Table.FromList(#"Filtered Historical", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
        {"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"}),
    
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{
        {"Date", Int64.Type}, {"Fcst_Cloud", Percentage.Type}, {"Fcst_Wind", Int64.Type}, {"Fcst_Solar", type number}, 
        {"Max_Cloud", type number}, {"Min_Cloud", type number}, {"Max_Wind", Int64.Type}, {"Min_Wind", Int64.Type}, 
        {"Max_Solar", type number}, {"Min_Solar", type number}, {"Count", Int64.Type}, 
        {"Min", Currency.Type}, {"Max", Currency.Type}, {"Mode", Currency.Type}, {"Median", Currency.Type}, {"Average", Currency.Type}})
in
    #"Changed Type"

And this is the resulting output:

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 0 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
4 0.75 10 0.2 0.95 0.55 25 -5 0.45 -0.05 6 4.5 10 4.5 6 6.5
6 0.11 35 0.8 0.31 -0.09 50 20 1.05 0.55 2 3 3 3 3 3

Forecast_Tbl OUTPUT](https://i.sstatic.net/8ozB2.png)

The issue is that when one forecast row (for example where Date "5" in output table should be) doesn't have any data points within the filtered range of Historical Data table, it return blank for the entire row.

What I would like it to do is return the original data from the Forecast_Tbl in the first 10 columns, for "Count" column show "0" (When no filtered Criteria are met), and use the previous rows "Average" column value (in this case 6.5) when no filtered Criteria are met. Below is the output I would like for the table to return:

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 0 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
4 0.75 10 0.2 0.95 0.55 25 -5 0.45 -0.05 6 4.5 10 4.5 6 6.5
5 0.1 99 0.99 0.3 -0.1 114 84 1.24 0.74 0 6.5
6 0.11 35 0.8 0.31 -0.09 50 20 1.05 0.55 2 3 3 3 3 3

I have tried using conditional if functions but unsuccessful.

Solution

  • How about

    ....
{"Count","Min","Max","Mode","Median","Average"})),

#"Converted to Table" = Table.FromList(#"Filtered Historical", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Column2", each try if Value.Is([Column1], type record ) then [Column1] else null otherwise Record.Combine({Forecast{[Index]}, [Count = 0, Average = #"Added Index"{[Index]-1}[Column1][Average]]})),

#"Expanded Column1" = Table.ExpandRecordColumn(Table.SelectColumns(#"Added Custom",{"Column2"}), "Column2", 
    {"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"}),
....